Average Response times using PHP/MySQL

Admin 1
Viewed 1652

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

tickets has the following columns:

  1. sequence
  2. ticketnumber
  3. datetime

and ticket_updates

  1. sequence
  2. ticket_seq
  3. datetime
  4. starttime
  5. 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?

mysql php
3 Answers

The average is sum of all values (in your case you have to convert datetime to UNIX timestamp) and then divide this sum to the total count. The conversion can be done via MySQL by issuing the 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.

user_6325240
Related