SUM not display correct sum MySQL

Question

I have this query:

SELECT sum(TIMEDIFF(`time_out`, `time_in`)) as `total` FROM `user_log` WHERE `uid` = '1' AND `time_out` <> '0000-00-00 00:00:00'

Then I calculate the seconds to hours:

$total_difference = (($difference->total) / 60 / 60);

But this is showing me 22,352125 hours, which is impossible...

I've been testing and I have 2 records showing up if I don't use SUM:

SELECT TIMEDIFF(`time_out`, `time_in`) as `total` FROM `user_log` WHERE `uid` = '1' AND `time_out` <> '0000-00-00 00:00:00'

This result is far below the 22 hours I get when I use SUM in my query. Any help? I don't know what I'm doing wrong.

Thanks!

@amdixon, I believe you should write it as an answer since it solved the problem — Ofir Baruch
– Ofir Baruch, Commented May 8, 2015 at 14:10

amdixon · Accepted Answer · 2015-05-08 14:17:13Z

3

Can adjust the query to use TIME_TO_SEC - this will convert the return value of TIMEDIFF to a numeric number of seconds, which can sensibly be summed

adjusted query

SELECT SUM(TIME_TO_SEC(TIMEDIFF(time_out, time_in))) as total 
FROM user_log 
WHERE uid = '1' AND time_out <> '0000-00-00 00:00:00'
;

answered May 8, 2015 at 14:17

amdixon

3,8338 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1