CODEWITHSUNDEEP
mysqldatetimestamp
V_RocKs
V_RocKs

Reputation: 134

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

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

Upvotes: 0

Views: 81

Answers (1)

Sven Tore
Sven Tore

Reputation: 987

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?

Upvotes: 1

Related Questions