What is the problem with this code to view the below results:
SELECT number, ResponseTime, TicketCreateTime, round(time_to_sec(timediff(ResponseTime, TicketCreateTime))/60,2) AS FRMins
FROM
(SELECT TE.id, T.number, T.ticket_id, TE.thread_id, TE.pid, T.created AS TicketCreateTime, TE.created AS ResponseTime, TE.type, TE.staff_id
FROM ost_ticket T
INNER JOIN ost_thread_entry TE ON (T.ticket_id) = TE.thread_id
WHERE T.created >= date('2020-08-31') AND TE.type = 'R' AND TE.id IN (SELECT min(id) FROM ost_thread_entry WHERE type = 'R' GROUP BY thread_id))AS FTRT_tbl
Response time
Hello,
Anyone can help!
You use PHPMyAdmin. What happens when you run your SQL query in it?
The result looks like the screenshot above. What exactly is the problem?
Oh I am sorry. I well explain this...
Osticket system version 1.14. On the system interface I see:
Ticket number 007582 (real information from Osticket interface):
Ticket create time:
08/31/2020 5:26 am
first response was:
08/31/2020 9:08 am
But in the query results first line say different information:
Ticket number 007582:
Ticket create time:
08/31/2020 03:26:02
First response is:
09/15/2020 10:15:22
So the date and time are not correct. and they have a serialization that I could not understand.
I'm working on Osticket version 1.14 and I need to export a table to excel with:
Ticket number - Creation time - close time - Time difference.
This to calculate response time KPI for Help Desk team.
I found this query but the date and time comes wrong or not like in my interface in each ticket.
So I need get the response time for one month tickets. And where the first response is saved in which table the database.
I hope its clear and sorry for bothering.
Thank you
Reda
It would seem that you are not adjusting the time for the timezone that you are in.