Trouble with mysql SUM of multiple datetimes

In my database, the times are stored as "datetimes"开发者_运维问答 (I don't know if this is the issue or not- I tried both datetime and timestamp). The structure looks like:

ID | START_DATE          | END_DATE
1  | 2011-10-10 08:15:00 | 2011-10-10 12:00:00
2  | 2011-10-11 09:00:00 | 2011-10-11 14:30:00
3  | 2011-10-12 08:45:00 | 2011-10-12 10:15:00

I am trying to get sum of total seconds between all the start and end times. Here's what I have:

$times = mysql_query("SELECT SUM(TIMEDIFF(end_date, start_date))
AS timesum FROM schedules");

$timetotal = mysql_fetch_assoc($times);

echo $timetotal['timesum'];

Now, the sum should be 38700, but when I run the above query, it returns 100500. What's wrong with the query?

You cannot sum datetime values - you need to convert to seconds first ....

SELECT SUm(time_to_sec(TIMEDIFF(end_date, start_date)))
AS timesum FROM schedules

You need to convert your TIMEDIFF to seconds using the TIME_TO_SEC function to correctly get the sum in terms of seconds:

$times = mysql_query("SELECT SUM(TIME_TO_SEC(TIMEDIFF(end_date, start_date)))
         AS timesum FROM schedules")

Without explicitly converting to seconds with TIME_TO_SEC the SUM function treats a TIMEDIFF as a floating point number. Give it a try on your first row to see what I mean:

$times = mysql_query("SELECT SUM(TIMEDIFF(end_date, start_date))
         AS timesum FROM schedules WHERE ID='1'")

should yield:

$times = 34500.000000