Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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
share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.5.22.2603\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </nos"], "copyright__copyright__copyright": ["IME_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:\n\n$times = mysql_query(\"SELECT SUM(TIMEDIFF(end_date, start_date))\n         AS timesum FROM schedules WHERE ID='1'\")\n\nshould yield:\n\n$times = 34500.000000\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}