MySQL：如何在一个组上 SUM() 一个 TIMEDIFF()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4102480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: How to SUM() a TIMEDIFF() on a group?
提问by Andrew
So I've got a set of results that looks something like this:
所以我得到了一组看起来像这样的结果：
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 |
------------------------------------------------------------------
Now I need to group the results by User_IDand SUM()TimeDiff. If I add a GROUP BYclause, it doesn't SUM()the TimeDiff (and I wouldn't expect it to). How can I SUM()the TimeDiffs for each User?
现在我需要按User_ID和SUM()TimeDiff对结果进行分组。如果我添加一个GROUP BY子句，它就不是SUM()TimeDiff（而且我不希望它）。我如何SUM()为每个用户设置 TimeDiff？
回答by OMG Ponies
Use:
用：
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
Steps:
脚步：
- Use TIME_TO_SECto convert TIME to seconds for math operation
- Sum the difference
- Use SEC_TO_TIMEto convert the seconds back to TIME
- 使用TIME_TO_SEC将 TIME 转换为秒进行数学运算
- 总结差异
- 使用SEC_TO_TIME将秒数转换回 TIME
Based on the sample data, I'd have just suggested:
根据示例数据，我刚刚建议：
SELECT t.user_id,
TIMEDIFF(MIN(t.startdate), MAX(t.enddate)) AS timediff
FROM MYTABLE t
GROUP BY t.user_id
NOTE: There is a bug in this code if you are using datetime. TIME_TO_SEC only converts the time section so you end up with big negatives if the clock goes past midnight. Use UNIX_TIMESTAMP instead to do the sum. Also SEC_TO_TIME maxes out at values greater than 3020399 seconds e.g. SELECT TIME_TO_SEC(SEC_TO_TIME(3020400)); If you see this value 838:59:59 you've reached the max and probably just need to divide by 3600 to just show hours.
注意：如果您使用的是日期时间，则此代码中存在一个错误。TIME_TO_SEC 只转换时间部分，所以如果时钟过了午夜，你最终会得到很大的负面影响。请改用 UNIX_TIMESTAMP 进行求和。此外 SEC_TO_TIME 的最大值大于 3020399 秒，例如 SELECT TIME_TO_SEC(SEC_TO_TIME(3020400)); 如果您看到此值 838:59:59，则您已达到最大值，可能只需要除以 3600 即可显示小时数。
回答by Wrikken
AFAIK, your only option is to cast to UNIX_TIMESTAMPs and do some integer calculations, substituting a random date (I chose 2000-01-01) for TIME columns without a date.
AFAIK，您唯一的选择是强制转换为UNIX_TIMESTAMPs 并进行一些整数计算，用随机日期（我选择 2000-01-01）代替没有日期的 TIME 列。
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;
Because it may seemyou can SUMTIME columns, but actually they will be cast to nasty integers or floats which will not follow time specifications (try it with a sum of minutes > 60 and you'll see what I mean).
因为看起来你可以SUMTIME 列，但实际上它们会被转换为讨厌的整数或浮点数，这些整数或浮点数不会遵循时间规范（尝试总分钟数 > 60，你会明白我的意思）。
For the ones who claim you can SUMtime columns:
对于那些声称可以为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
回答by a1ex07
Will it work for your?
对你有用吗？
SELECT User_ID, TIME(SUM(TIMEDIFF(EndTime, StartTime))) AS TimeDiff
FROM MyTable GROUP BY User_ID
SELECT User_ID, TIME(SUM(TIMEDIFF(EndTime, StartTime))) AS TimeDiff
FROM MyTable GROUP BY User_ID
回答by David L
I'll suggest you use TO_SECONDSinstead:
我建议你改用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
回答by Bobbi Lusic
This worked for me in a time tracking app. In short, I converted all intervals into seconds, added them up, and converted back to TIME format. I am a beginner, so please excuse any clumsiness in the code. I always welcome feedback.
这在时间跟踪应用程序中对我有用。简而言之，我将所有间隔转换为秒，将它们相加，然后转换回 TIME 格式。我是初学者，所以请原谅代码中的任何笨拙。我总是欢迎反馈。
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);
CREATE table time_segments (segment_id int not null auto_increment主键, sign_in DATETIME, sign_out DATETIME, Seconds INT not null, Display TIME not null, user_id INT);
UPDATE time_segmentsSET Seconds=TIME_TO_SEC(-TIMEDIFF(sign_in,sign_out));
更新time_segmentsSET Seconds=TIME_TO_SEC(-TIMEDIFF( sign_in, sign_out));
UPDATE time_segmentsSET Display=SEC_TO_TIME(Seconds);
更新time_segmentsSET Display=SEC_TO_TIME(秒);
INSERT INTO time_segments (sign_in, sign_out, user_id) VALUES ('2019-03-12 14:01:00', '2019-03-12 16:45:00', 1), ... ;
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;
mysql> select * from time_segments;
| segment_id | sign_in | sign_out | Seconds | Display |
| 段_id | 登录 | 退出 | 秒 | 显示 |
| 1 | 2019-03-12 14:01:00 | 2019-03-12 16:45:00 | 9840 | 02:44:00 |
| 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;
mysql> SELECT SEC_TO_TIME(SUM(Seconds)) AS 'Payperiod hours' FROM time_segments;
| Payperiod Hours | +-----------------+ | 49:29:00 |
| 发薪时间 | +---+ | 49:29:00 |
回答by Hymany
If you are experiencing this bug, a good alternative is to use TIMESTAMPDIFF and then get the sum of the column you create.
如果您遇到此错误，一个不错的选择是使用 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
Note of caution: You will get very close to the actual number of hours but may be a few decimal points off. See floating point rounding for more details.
注意事项：您将非常接近实际小时数，但可能会偏离几个小数点。有关更多详细信息，请参阅浮点舍入。
