PHP – Get Average Handling Time (AHT) from mySQLi Database

I followed this to try to get the AHT from my database.

So, the database structure is:

 _____________________________________________________________________________________
|                                                                                     |
|     ticketid    tecnico_id        time_at      visit_hour_in     visit_hour_out     |
|_____________________________________________________________________________________|
|        1             3             08:15            16:03            17:00          |
|        2             7             10:45            11:03            13:00          |
|        3             9             08:05            12:03            14:00          |
|        4             6             08:43            12:03            13:30          |
|        5            10             13:30            14:03            15:45          |
|_____________________________________________________________________________________|

Therefore, I would like to get the average handling time by tecnico_id.

I tried with:

SELECT tecnico_id,
  AVG((time_at) - (visit_hour_in)) AS responseTime,
  AVG((visit_hour_in) - (visit_hour_out)) AS activityTime,
  AVG((time_at) - (visit_hour_out)) AS handlingTime
FROM `ticket` GROUP BY tecnico_id ORDER BY `ticket`.`tecnico_id` ASC

It outputs this:

 ___________________________________________________________________________
|                                                                           |
|        tecnico_id     responseTime    activityTime        handlingTime    |
|___________________________________________________________________________|
|        3                    0              0                    0         |
|        5                 13.6              0                  13.6        |
|        6      5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        7      5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        8      5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        9      5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        10     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        11     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        18     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        19     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        20     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        21     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        22     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        23     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|        24     5.791946308724833  -0.37583892617449666   5.416107382550336 |
|___________________________________________________________________________|

The 5.791946308724833, -0.37583892617449666 and 5.416107382550336 are not repeated, I just didn’t wanted to format the table.

To convert it to hours, min and secs I use gmdate('H:i:s', '5.791946308724833'), (as an example), however… The results are not accurate as they should be. What I need to do, is getting the difference between those fields, then getting the average, so I can say, as an example:

tecnico_id’s #3 responseTime is 02 hours and 3 minutes, his activity time is 0 hours and 48 minutes and his AHT per ticket is 3 hours and 0 minutes.

How can I achieve this?

Answer

The way to solve this is actually easy. I used this code to make it work.

SELECT tecnico_id, COUNT(tecnico_id) as cantidad_tecnico ,
 SUM(TIMESTAMPDIFF(MINUTE,convert(time_at,time),convert(visit_hour_in, time))) as tiempo, 
 (TIMESTAMPDIFF(MINUTE,convert(time_at,time),convert(visit_hour_in, time))/COUNT(tecnico_id)) as promedio
FROM ticket group by tecnico_id asc