Average Response times using PHP/MySQL

mysql, php

Solution

From your chat wit Andy Jones I understand that you define the response time of a ticket as the time elapsed between `tickets.datetime` and the first `ticket_updates.datetime` for that ticket. In that case, the following query returns the average response time in seconds for tickets added in the last 7 days.

SELECT avg(response_seconds)
  FROM (
     SELECT time_to_sec(timediff(min(u.datetime), t.datetime)) AS response_seconds
       FROM tickets t
       JOIN ticket_updates u
         ON t.ticketnumber = u.ticket_seq
      WHERE t.datetime > now() - INTERVAL 7 day
      GROUP BY t.ticketnumber ) AS r

Problem

I have 2 tables in a MySQL Database. one called `tickets` and the other is `ticket_updates` tickets has the following columns: - sequence - ticketnumber - datetime and ticket_updates - sequence - ticket_seq - datetime - starttime - endtime the `ticket_seq` column in the `ticket_updates` table links with the `ticketnumber` column in the `tickets` table. There may be multiple rows in `ticket_updates` linking to one row in the `tickets` table. I want to show an average response time for how long it takes for the tickets to be replied to. the `datetime` column in both tables is a full timestamp of when the row was added/inserted `(Y-m-d H:i:s)` how can i show average response times for, say the last week?

Original source