Geek Answers Handbook

Average response time using PHP / MySQL

I have 2 tables in a MySQL database. one is called tickets and the other is ticket_updates

have the following columns:

  • sequence
  • ticketnumber
  • date and time

and ticket_updates

  • sequence
  • ticket_seq
  • date and time
  • start time
  • end time

the ticket_seq column in the ticket_updates table refers to the ticketnumber column in the tickets table. There can be several rows in ticket_updates associated with one row in the tickets table.

I want to show the average response time to how long it takes to answer the tickets. The datetime column in both tables is the full timestamp when the row was added / inserted ( (Ymd H:i:s)

How can I show the average response time, say, last week?

+7
php mysql
source share
3 answers

From your chat with Andy Jones. I understand that you define the ticket response time as the elapsed time between tickets.datetime and the first ticket_updates.datetime for this ticket. In this 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
+2
source share

I put together a complete example. I suggested some things about your data - if the columns are of the wrong type, change them accordingly. I am also going to return the average update time in seconds.

 CREATE TABLE tickets ( sequence int(10) not null auto_increment primary key, ticket_number int(10) not null default 0, date_added timestamp, index ticket_number_index (ticket_number) ); INSERT INTO tickets (ticket_number, date_added) VALUES (1, '2013-12-01 01:00:00'), (2, '2013-12-15 02:00:00'), (3, '2013-12-10 03:00:00'), (4, '2013-12-13 04:00:00'), (5, '2013-12-17 05:00:00'); CREATE TABLE ticket_updates ( sequence int(10) not null auto_increment primary key, ticket_number int(10) not null default 0, date_added timestamp, starttime datetime, endtime datetime, index ticket_number_index (ticket_number) ); INSERT INTO ticket_updates (ticket_number, starttime, endtime, date_added) VALUES (1, '2013-12-01 01:01:00', '2013-12-01 01:02:00', '2013-12-01 01:02:00'), (1, '2013-12-01 01:01:00', '2013-12-01 01:02:00', '2013-12-01 01:02:00'), (2, '2013-12-15 02:01:00', '2013-12-15 02:02:00', '2013-12-15 02:02:00'), (2, '2013-12-15 02:01:00', '2013-12-15 02:02:00', '2013-12-15 02:02:00'), (2, '2013-12-15 02:01:00', '2013-12-15 02:02:00', '2013-12-15 02:02:00'), (2, '2013-12-15 02:02:00', '2013-12-15 02:02:50', '2013-12-15 02:02:50'), (4, '2013-12-13 04:01:00', '2013-12-13 04:02:00', '2013-12-13 04:02:00'), (4, '2013-12-13 04:01:00', '2013-12-13 04:05:30', '2013-12-13 04:05:30'), (5, '2013-12-17 05:01:00', '2013-12-17 05:03:00', '2013-12-17 05:03:00');

Then you can run this query ...

 SELECT SUM(TO_SECONDS(ticket_updates.date_added) - TO_SECONDS(tickets.date_added)) / count(*) as update_time FROM tickets, ticket_updates WHERE tickets.ticket_number = ticket_updates.ticket_number AND ticket_updates.date_added > now() - INTERVAL 1 WEEK

Not sure if this is exactly what you are looking for, but this (and the violin) may help you get closer to the answer.

See Fiddle: http://sqlfiddle.com/#!2/4c0acc/1

+1
source share

The average value of the sum of all values ​​(in your case, you need to convert the date and time to a UNIX timestamp), and then divide this amount into a total account. The conversion can be done through MySQL by issuing a command, for example

 SELECT unix_timestamp(str_to_date('30/05/2011','%d/%m/%Y'));

or in PHP when displaying the results. It depends on the logic and structure of your project.

0
source share

More articles:


All Articles