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.


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



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




All Articles