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
Chakradar raju
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
mr_eclair
source
to share
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
veeTrain
source
to share