MySQL - How to SUM times?

Question

I have a table that contains date-time values in this format:

START

1/13/2009 7:00:00AM

END

1/13/2008 2:57:00PM

I use the 'str to date' function to convert these into a date-time format.

How do I calculate a difference between them? And then sum it all up so that it displays in total hours (ie total hours for week is 40:53).

I was trying the timediff function but the results don't sum.

Answer 1

Check out the MySQL DateDiff Function

SELECT SUM(DATEDIFF(endtime, starttime)) 
FROM somerecordstable
WHERE starttime > '[some date]' AND endtime <= '[some date]'

You could also try:

SELECT SUM(TIMEDIFF(endtime, starttime)) 
FROM somerecordstable
WHERE starttime > '[some date]' AND endtime <= '[some date]'

I haven't tested the second one, but I know the first one should work.

Answer 2

SELECT some_grouping_column, SUM(TIMEDIFF(datecol1,datecol2)) as tot_time
FROM my_table
GROUP BY some_grouping_column;

Answer 3

Try looking into UNIX_TIMESTAMP and SEC_TO_TIME.

You would sum up the differences between the timestamps, then use that value (would be in milliseconds) to get the time:

SELECT SEC_TO_TIME(time_milis / 1000)
FROM (
   SELECT SUM(UNIX_TIMESTAMP(date1) - UNIX_TIMESTAMP(date2)) as time_milis
   FROM table
)

Answer 4

Use TIMEDIFF().

SUM(TIMEDIFF(end, start))

Answer 5

i think you have to use this code... it works :)

SELECT

SEC_TO_TIME( SUM( TIME_TO_SEC( time ) ) ) AS total_time

FROM time_table;

Answer 6

super ça marche super bien. impeccable......

Answer 7

Hello All I want to perform the same operation on TIME datatypes. I have starttime and endtime columns in which I will store the Time values like [HH:MM:SS]. In this case the query is not working and the sec_to_time() function is unable to convert the seconds value which is more than '3020399'. In my case I will have seconds total more than the default value.. How can I get the solution for this?

Please someone help.. I am browsing all the Mysql Forms but couldn't get the appropriate solution

Thanks in Advance