sql - MySQL: How to SUM() a TIMEDIFF() on a group?

Question

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

Answer 1

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:

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   

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.