Clever Geek Handbook

Aggregated SUM function for time values ​​in MySQL

I am trying to create a ranklist in which I have to sum up a lot of time to get the total time when I tried to use SUM (TIMEDIFF ( finishTime

, 'starttime')) in MySQL. I noticed that adding two times the duration happens as if they were two normal numbers, that is, if I add 00:00:50 and 00:00:50, I get 00:01:00 as a response.

TO_SECOND is not available in MySQL 5.1.

+3


source to share


2 answers 


SUM(TIMEDIFF(TIME_TO_SEC(finishtime) - TIME_TO_SEC(starttime)));


Use TIME_TO_SEC to convert TIME to seconds for math operation

+2


source


In case you've worked with datetime timestamps, I've found that I can get more reliable results using the following method:



 SUM( UNIX_TIMESTAMP( finishtime ) - UNIX_TIMESTAMP( starttime ) ) /3600
+1


source






More articles:


All Articles