MYSQL按小时分组并求和差异，但按小时分开结果

提利纳

我有以下数据集

start_time              end_time
2017-09-12 12:08:55     2017-09-12 12:10:57
2017-09-12 12:15:10     2017-09-12 12:19:27
2017-09-12 12:24:54     2017-09-12 12:25:35
2017-09-12 12:27:32     2017-09-12 13:20:25
2017-09-12 13:20:25     2017-09-12 13:22:23
2017-09-12 13:32:01     2017-09-12 13:33:57
2017-09-12 13:45:15     2017-09-12 13:46:38
2017-09-12 13:52:52     2017-09-12 13:54:20
2017-09-12 13:59:28     2017-09-12 14:02:32
2017-09-12 14:04:37     2017-09-12 14:07:34
2017-09-12 14:12:49     2017-09-12 14:13:03
2017-09-12 14:34:09     2017-09-12 14:35:47
2017-09-12 14:40:52     2017-09-12 14:46:46
2017-09-12 14:51:57     2017-09-12 14:54:28

我试图找到按小时分组的时差总和。由于一些程序块在一小时内开始，但在下一个小时内结束，因此计算出的总和不是我想要的方式。例如，如果我们采用2017-09-12 12:27:32-2017-09-12 13:20:25的总差异添加到第12小时，而从第13小时开始忽略。

我正在使用以下查询进行分组和求和

SELECT 
DATE_FORMAT(start_time, "%H") as h, 
SEC_TO_TIME(SUM(TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))) AS timediff 
FROM history 
WHERE start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
GROUP BY hour(start_time)

并得到结果

h   timediff
12  00:59:53
13  00:09:49
14  00:13:14

有没有办法打破像2017-09-12 12:27:32-2017-09-12 13:20:25这样的块并将差异分别添加到mysql中的各个小时块？

首选输出

h   timediff
12  00:39:28
13  00:27:42
14  00:15:46

（从2017-09-12 12:27:32-2017-09-12 13:20:25，32m 28s应该去12 h块，20m 25s应该去13h块）

恰图拉

为此使用mysql子查询。首先将数据划分为每个小时，然后尝试添加时间段。

SELECT SEC_TO_TIME(SUM(t.h)) timediff, t.hc FROM 
(
SELECT start_time, end_time,
(  UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP( start_time )  ) h, HOUR( start_time ) hc from history 
WHERE HOUR(end_time) = HOUR(start_time) AND start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
UNION
SELECT start_time, end_time,
(  UNIX_TIMESTAMP( end_time ) - UNIX_TIMESTAMP(DATE_FORMAT(end_time, '%Y-%m-%d %H:00:00'))) call_time, HOUR( end_time ) hc from history 
WHERE HOUR(end_time) > HOUR(start_time) AND start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
UNION
SELECT start_time, end_time,
(  UNIX_TIMESTAMP(DATE_FORMAT(end_time, '%Y-%m-%d %H:00:00')) - UNIX_TIMESTAMP( start_time )  ) call_time, HOUR( start_time ) hc from history 
WHERE HOUR(end_time) > HOUR(start_time) AND start_time BETWEEN '2017-09-12 12:00:00' and '2017-09-12 15:00:00'
) t GROUP BY t.hc

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。