php 平均时间,PHP-从mySQLi数据库获取平均处理时间(AHT)

所以,数据库结构是:

_____________________________________________________________________________________

| |

| 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 |

|_____________________________________________________________________________________|

因此,我想得到平均处理时间

tecnico_id

.

我试过:

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

它输出:

___________________________________________________________________________

| |

| 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 |

|___________________________________________________________________________|

这个

5.791946308724833

,

-0.37583892617449666

5.416107382550336

没有重复,我只是不想格式化表格。

把它转换成小时,分钟和秒

gmdate('H:i:s', '5.791946308724833')

应该是

需要做的

tecnico id的3响应时间是02小时3分钟,他的活动时间是0小时48分钟,每张票的AHT是3小时0分钟。

我怎样才能做到这一点?