Best way to sum the accumulated time between time stamps across multiple rows in MYSQL

Question

I am trying to come up with the sum of time in seconds that users have logged in for a given day, yesterday, current week (starting on Monday) and current month.

I am having luck when doing the day and week, but not the month.

Here is my data set:

| ID   | userID   | blank| inDateTime          | outDateTime         |
 --------------------------------------------------------------------
| 5451 |      134 |      | 2014-07-30 13:44:26 | 2014-07-31 03:11:03 |
| 5510 |      134 |      | 2014-07-31 12:18:15 | 2014-07-31 13:18:24 |
| 5526 |      134 |      | 2014-07-31 17:01:48 | 2014-08-01 02:45:54 |
| 5602 |      134 |      | 2014-08-01 23:34:12 | 2014-08-02 00:11:57 |
| 5613 |      134 |      | 2014-08-02 01:11:02 | 2014-08-02 04:46:02 |
| 5697 |      134 |      | 2014-08-03 16:31:07 | 2014-08-03 22:01:31 |
| 5712 |      134 |      | 2014-08-03 22:05:51 | 2014-08-03 22:07:15 |
| 5751 |      134 |      | 2014-08-04 13:42:41 | 2014-08-05 02:28:13 |
| 5807 |      134 |      | 2014-08-05 11:55:56 | 2014-08-06 02:09:18 |
| 5871 |      134 |      | 2014-08-06 12:00:00 | 2014-08-06 18:09:45 |
| 5911 |      134 |      | 2014-08-06 22:24:18 | 2014-08-07 02:12:53 |
| 6124 |      134 |      | 2014-08-10 14:32:56 | 2014-08-10 22:02:25 |
| 6153 |      134 |      | 2014-08-10 23:13:38 | 2014-08-10 23:24:55 |
| 6182 |      134 |      | 2014-08-11 12:53:57 | 2014-08-12 00:44:11 |
| 6230 |      134 |      | 2014-08-12 11:32:31 | 2014-08-12 19:06:25 |
| 6269 |      134 |      | 2014-08-13 00:41:27 | 2014-08-13 03:48:36 |
| 6303 |      134 |      | 2014-08-13 12:38:28 | 2014-08-14 02:00:22 |
| 6358 |      134 |      | 2014-08-14 11:50:09 | 2014-08-15 01:19:02 |
| 6418 |      134 |      | 2014-08-15 10:59:32 | 2014-08-15 15:07:35 |
| 6528 |      134 |      | 2014-08-17 13:07:48 | 2014-08-17 15:37:36 |
| 6543 |      134 |      | 2014-08-17 19:21:09 | 2014-08-18 00:31:00 |
| 6579 |      134 |      | 2014-08-18 10:53:36 | 2014-08-18 23:46:23 |
| 6662 |      134 |      | 2014-08-19 13:51:47 | 2014-08-20 01:00:13 |
| 6704 |      134 |      | 2014-08-20 10:18:07 | 2014-08-21 01:25:22 |
| 6788 |      134 |      | 2014-08-21 16:19:16 | 2014-08-22 01:03:32 |
| 6828 |      134 |      | 2014-08-22 10:23:35 | 2014-08-22 23:33:31 |
| 6952 |      134 |      | 2014-08-24 12:40:03 | 2014-08-24 18:56:43 |
| 6972 |      134 |      | 2014-08-24 20:23:40 | 2014-08-25 00:01:22 |
| 7061 |      134 |      | 2014-08-26 05:31:51 | 2014-08-26 05:42:59 |
| 7062 |      134 |      | 2014-08-26 06:08:16 | 2014-08-26 06:08:19 |
| 7067 |      134 |      | 2014-08-26 07:55:26 | 2014-08-26 07:55:28 |
| 7068 |      134 |      | 2014-08-26 07:55:32 | 2014-08-26 07:55:34 |
| 7069 |      134 |      | 2014-08-26 07:55:37 | 2014-08-26 07:55:40 |
| 7070 |      134 |      | 2014-08-26 07:55:50 | 2014-08-26 07:55:54 |
| 7071 |      134 |      | 2014-08-26 07:57:13 | 2014-08-26 07:57:15 |
| 7072 |      134 |      | 2014-08-26 07:57:47 | 2014-08-26 07:57:51 |
| 7073 |      134 |      | 2014-08-26 07:58:41 | 2014-08-26 07:58:44 |

When I use for todays calculation:

SELECT SUM( outDateTime - inDateTime ) AS minToday
FROM time_log
WHERE userID =134
AND DATE(  `inDateTime` ) = DATE( NOW( ) ) 
AND outDateTime !=  '0000-00-00 00:00:00'

I get the correct answer of 1131 seconds.

When I try doing it for the month with:

SELECT SUM( outDateTime - inDateTime ) AS minToday
FROM time_log
WHERE userID =134
AND  `inDateTime` > DATE_SUB( NOW( ) , INTERVAL 1 
MONTH ) 
AND outDateTime !=  '0000-00-00 00:00:00'

I get: 84382713

Which is over 2 1/2 years worth of seconds and cannot be correct.

Why are the days timestamps properly computed and the months did not?

How should I properly be querying for this kind of data?

I modified the second query to give me to total seconds as a positive integer. I also modified the time selection to only choose days from the current month.

    SELECT SUM( TIMESTAMPDIFF(SECOND,inDateTime, outDateTime ) ) AS minToday
    FROM time_log
    WHERE userID =".$id."
    AND YEAR(`inDateTime`) = YEAR(CURDATE()) AND MONTH(`inDateTime`) = MONTH(CURDATE()) 
    AND outDateTime !=  '0000-00-00 00:00:00'",true);

First - look into using BETWEEN instead. Second - test how your server handles BETWEEN. Sometimes it's inclusive, sometimes not. You'll have to actually do a test output to see which is happening. — durbnpoisn, Aug 26 '14 at 13:00
There are no bad data rows with a timestamp greater than today. — V_RocKs, Aug 26 '14 at 13:33

score 1 · Accepted Answer · answered Aug 26 '14 at 13:07

You can't to the math directly like you're trying to do. You either have to convert to unix timestamp, so that you have two integers when substracting or use the timestampditt function.

SELECT SUM( UNIX_TIMESTAMP(outDateTime) - UNIX_TIMESTAMP(inDateTime) ) AS minToday
FROM time_log
WHERE userID =134
AND  `inDateTime` > DATE_SUB( NOW( ) , INTERVAL 1 
MONTH ) 
AND outDateTime !=  '0000-00-00 00:00:00'

Or you can do a: (preferred)

SELECT TIMESTAMPDIFF(MINUTE,outDateTime inDateTime ) AS minToday
FROM time_log
WHERE userID =134
AND  `inDateTime` > DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
AND outDateTime !=  '0000-00-00 00:00:00'

Not that your field name "minToday" is not correct for any of them, since the first alternative results in seconds, and the second results in minutes this month.

Final thing to check is your where statement. If you do a

select * from time_log
WHERE userID =134
AND  `inDateTime` > DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) 
AND outDateTime !=  '0000-00-00 00:00:00'

Do you only get the expected rows?

With that where statement I am getting all the way back to 2014-07-27 for that user. Which is not what I actually want. I want 2014-08-01 to current date. Not 30 days or so back from the current date. — V_RocKs, Aug 26 '14 at 13:23
Then replace DATE_SUB(....), and look at e.g http://stackoverflow.com/questions/11808232/how-do-i-get-the-first-day-of-the-current-month — Sven Tore, Aug 27 '14 at 06:40

Best way to sum the accumulated time between time stamps across multiple rows in MYSQL

1 Answers1