logo标签列表

MySQL：如何对一组数据使用SUM()函数计算TIMEDIFF()的总和？

sqlmysqlaggregate-functions
23

所以我有一组结果，看起来像这样：

SELECT User_ID, StartTime, EndTime, TIMEDIFF(EndTime, StartTime) AS TimeDiff
FROM MyTable

------------------------------------------------------------------
| User_ID |       StartTime     |         EndTime     | TimeDiff |
------------------------------------------------------------------
|    1    | 2010-11-05 08:00:00 | 2010-11-05 09:00:00 | 01:00:00 |
------------------------------------------------------------------
|    1    | 2010-11-05 09:00:00 | 2010-11-05 10:00:00 | 01:00:00 |
------------------------------------------------------------------
|    2    | 2010-11-05 06:30:00 | 2010-11-05 07:00:00 | 00:30:00 |
------------------------------------------------------------------
|    2    | 2010-11-05 07:00:00 | 2010-11-05 09:00:00 | 02:00:00 |
------------------------------------------------------------------
|    2    | 2010-11-05 09:00:00 | 2010-11-05 10:00:00 | 01:00:00 |
------------------------------------------------------------------

现在我需要按User_ID和SUM()的TimeDiff分组结果。如果我添加GROUP BY子句，它不会对TimeDiff进行SUM()（我也不希望它这样做）。我应该如何对每个用户的TimeDiffs进行SUM()？

6个回答
53

使用：

  SELECT t.user_id,       
         SEC_TO_TIME(SUM(TIME_TO_SEC(t.endtime) - TIME_TO_SEC(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

步骤：
  1. 使用TIME_TO_SEC将时间转换为秒进行数学运算
  2. 求出差值的总和
  3. 使用SEC_TO_TIME将秒转换回时间
根据样本数据，我建议只需：
  SELECT t.user_id,       
         TIMEDIFF(MIN(t.startdate), MAX(t.enddate)) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

注意：如果您正在使用datetime，则此代码存在错误。TIME_TO_SEC仅转换时间部分，因此如果时钟过了午夜，您将得到大的负值。改用UNIX_TIMESTAMP进行求和。而且SEC_TO_TIME在大于3020399秒的值上达到最大值，例如SELECT TIME_TO_SEC（SEC_TO_TIME（3020400））;如果您看到这个值838:59:59，则已达到最大值，可能只需要除以3600即可显示小时。

该死，早知道TIME_TO_SEC这个函数了，我就不用用CONCAT和随机日期来凑了...给你点赞！ - Wrikken
1
这就是OMG所暗示的（如果你的测试数据中没有间隙，第二个解决方案将更加优化 :)） - Wrikken
这似乎给我负数的日期值，横跨午夜，导致计算错误。有人以前遇到过这个问题吗？ - Ben
我不确定我理解你对这个错误的解决方法。我的结果得到了838:59:59的最大值，将我的答案除以3600会得到错误的小时数。如果开始和结束时间都达到最大值并且你的答案也达到了最大值，你能否提供一个解决方法的示例？谢谢。 - Jacky
请删除您的示例查询“根据示例数据，我只会建议” ，因为它会误导人们认为它是已接受答案的一部分。对于大多数实际情况，它将返回不准确的结果。 - yg-dba
显示剩余5条评论
4
据我所知，你唯一的选择是将其转换为UNIX_TIMESTAMP并进行整数计算，对于没有日期的时间列，可以替换为随机日期（我选择了2000-01-01）。
SELECT TIMEDIFF(
    DATE_ADD('2000-01-01 00:00:00',
       INTERVAL 
       SUM(UNIX_TIMESTAMP(CONCAT('2000-01-01 ',TimeDiff)) - UNIX_TIMESTAMP('2000-01-01 00:00:00')
       SECOND),
    '2000-01-01 00:00:00')
FROM MyTable;

由于看起来你可以SUM时间列，但实际上它们将被转换为不符合时间规格的丑陋整数或浮点数（如果使用分钟之和大于60进行求和，你就会明白我的意思）。

对于那些声称可以SUM时间列的人：

mysql> create table timetest(a TIME);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO timetest VALUES ('02:00'),('03:00');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SUM(a) FROM timetest;
+--------+
| SUM(a) |
+--------+
|  50000 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| 05:00:00     |
+--------------+
1 row in set (0.00 sec)

mysql> -- seems ok, but wait
mysql> INSERT INTO timetest VALUES ('02:30');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| 07:30:00     |
+--------------+
1 row in set (0.00 sec)

mysql> -- and now, oh ye unbelievers:
mysql> INSERT INTO timetest VALUES ('01:40');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT TIME(SUM(a)) FROM timetest;
+--------------+
| TIME(SUM(a)) |
+--------------+
| NULL         |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> -- why is that? because it uses integer arithmetic, not time - arithmetic:
mysql> SELECT SUM(a) FROM timetest;
+--------+
| SUM(a) |
+--------+
|  87000 |
+--------+
1 row in set (0.00 sec)

mysql> -- that cannot be cast to time
1
不会的，我马上会编辑一个例子，其中分钟数不为“0”。 - Wrikken
1
是的，我的错，我没有花足够的时间看到1:59 + 1:59不等于3:18 :-p。 - Vincent Savard
哈哈，在这里，没事的，你能相信我曾经在很久很久以前的几个星期里，一个错误的假设上运行了一个工资系统吗？到处都是愤怒的人群 :) - Wrikken
抱歉，我应该更具体一些。开始/结束时间实际上是日期时间。这会改变你的答案吗？ - Andrew
1
在这种情况下，OMG的答案仍然更为恰当：SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(EndTime, StartTime))))。 - Wrikken
1

我建议您使用TO_SECONDS：

SELECT t.user_id,       
         SEC_TO_TIME(SUM(TO_SECONDS(t.endtime) - TO_SECONDS(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id
1
如果你遇到了这个 bug，一个好的替代方案是使用 TIMESTAMPDIFF 然后获取你创建的列的总和。
select(sum(df.elapse)/60 as diff
from( SELECT c1.start, c1.end, TIMESTAMPDIFF(MINUTE,c1.start,c1.end) as elapse
from c1) df

注意事项：您将非常接近实际工作小时数，但可能会有几个小数点的偏差。有关更多详细信息，请参阅浮点舍入。
0

这对我在一个时间跟踪应用程序中起作用。简而言之，我将所有间隔转换为秒，相加，然后再转换回时间格式。 我是一个初学者，所以请原谅代码中的任何笨拙。我总是欢迎反馈。

CREATE table time_segments (segment_id int not null auto_increment primary key, sign_in DATETIME, sign_out DATETIME, Seconds INT not null, Display TIME not null, user_id INT);

UPDATE time_segments SET Seconds=TIME_TO_SEC(-TIMEDIFF(sign_in,sign_out));

UPDATE time_segments SET Display=SEC_TO_TIME(Seconds);

INSERT INTO time_segments (sign_in, sign_out, user_id) VALUES ('2019-03-12 14:01:00', '2019-03-12 16:45:00', 1), ... ;

mysql> select * from time_segments;

| segment_id | sign_in | sign_out | Seconds | Display |

| 1 | 2019-03-12 14:01:00 | 2019-03-12 16:45:00 | 9840 | 02:44:00 |

mysql> SELECT SEC_TO_TIME(SUM(Seconds)) AS 'Payperiod Hours' FROM time_segments;

| 工资期小时数 | +-----------------+ | 49:29:00 |

-1

这个对你有用吗？

SELECT User_ID, TIME(SUM(TIMEDIFF(EndTime, StartTime))) AS TimeDiff
FROM MyTable GROUP BY User_ID

2
据我所知，在 TIME 值上使用 SUM 会给出糟糕的结果（01:30 + 02:42 变成了 0130 + 0242 => 0372 => 无效时间 / NULL）。 - Wrikken
1
Wrikken 是正确的 - 在根据示例数据进行测试时，我得到了 null 作为结果。 - OMG Ponies
+1。你们是对的。猜错了，我试过了，如果时间差没有分钟/秒部分，它可以正常工作。我忽略了时间也可能有分钟/秒的事实 :( - a1ex07
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接