MySQL:如何对组中的TIMEDIFF()求和? [英] MySQL: How to SUM() a TIMEDIFF() on a group?

问题描述

所以我得到了一组看起来像这样的结果:

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

现在，我需要将结果按User_ID和SUM() TimeDiff分组.如果我添加GROUP BY子句，则它不会SUM() TimeDiff(而且我也不希望它).如何为每个用户SUM() TimeDiff?

Now I need to group the results by User_ID and SUM() TimeDiff. If I add a GROUP BY clause, it doesn't SUM() the TimeDiff (and I wouldn't expect it to). How can I SUM() the TimeDiffs for each User?

推荐答案

使用:

  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 将TIME转换为秒以进行数学运算
2. 求和求和
3. 使用 SEC_TO_TIME 将秒转换回TIME

1. Use TIME_TO_SEC to convert TIME to seconds for math operation
2. Sum the difference
3. Use SEC_TO_TIME to convert the seconds back to 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   

注意:如果您使用日期时间，则此代码中有错误. TIME_TO_SEC只转换时间部分，所以您最终会占用很大的时间 如果时钟经过午夜，则为负数.改用UNIX_TIMESTAMP 做总和.同样，SEC_TO_TIME的最大值大于 3020399秒例如SELECT TIME_TO_SEC(SEC_TO_TIME(3020400));如果你 看到这个值838:59:59您已经达到最大值，可能只需要 用3600除以仅显示小时数.

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.

这篇关于MySQL:如何对组中的TIMEDIFF()求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！