Sounds like it would work to me. But it's a lot harder to get the "Ticket changed to Resolved" datetime stamp.
created, updated, lastupdate, closed, reopened, and est_duedate is located in the ost_ticket table, and custom states are not. You would have to get the created from there, and then you would have to figure out the thread system to get the timestamp from the ost_thread_event table.
note: this is not as hard as it sounds!
You can look this up like this:
Say you have Ticket # 411152.
Looking at the ost_ticket table you can see that its internal ticket id is: 16511.
SQL QUERYSELECT ticket_id FROM ost_ticket WHERE number=411152`
You can then use that id to get the thread id from ost_thread.
Looking at the ost_thread table you can see that its id is 21102.
SELECT id FROM ost_thread WHERE object_id=16511 AND object_type = T
You can then use that to look up the timestamp from ost_thread_event.
Looking at the ost_thread_event table you can see that its timestamp is: 2019-09-05 15:57:48.
SELECT id,data,timestamp FROM ost_thread_event WHERE data = '{"status":[2,"Resolved"]}' AND thread_id = '21102'
It's worth noting that there are cases where:
- this {"status":[2,"Resolved"]} might not match what I have in mine.
- you can get multiple dates. If a ticket is marked resolved, and then change back to open, and then changed back to resolved. you can add this to the end of the query to just get one date (the newest)
ORDER BY timestamp DESC LIMIT 1
Does that answer your questions?
