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

4 days later

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.

Write a Reply...