mysql计算特定小时内的总分钟数

Question

我有一个类似的数据

    2017-03-22 10:57:01
    2017-03-22 10:57:09
    2017-03-22 10:59:35
    2017-03-22 10:59:55
    2017-03-22 11:00:01
    2017-03-22 11:08:16
    2017-03-22 11:39:07
    2017-03-22 11:50:37
    2017-03-22 11:59:57
   ..........
ex. 10 AM total minutes worked 45

    tracking_dtls 

  device_id    tracking_dt_time
        1        2017-03-22 10:57:01
        1        2017-03-22 10:57:09
        1        2017-03-22 10:59:35
        1        2017-03-22 10:59:55
        1        2017-03-22 11:00:01
        1        2017-03-22 11:08:16
        1        2017-03-22 11:39:07
        1        2017-03-22 11:50:37
        1        2017-03-22 11:59:57

From tracking_dtls  table calculate total working minutes for a all hours 

在此数据中分别计算所有时间的工作分钟。我该怎么做，有人可以帮助我吗。

Answer 1

您需要仔细进行分组并取时间最大值减去最小值的差值。

select HOUR(tracking_dt_time),
(TIME_TO_SEC(max(tracking_dt_time))-TIME_TO_SEC(min(tracking_dt_time)))/60 as minutes from tracking_dtls group by HOUR(tracking_dt_time)