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

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 2

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.