Computers never do what you want them to do, only what you tell them to do. Unless there’s a bug. Then you’re just screwed…
Grrrr… nothing like a MySql bug to really make your day.
I had need to generate a sum for a column of duration values with the type of TIME. Orginally I used this as part of my query:
sum(call_duration) as totalhours
Turns out this does not work properly and has apparently been a problem since 2005!
http://bugs.mysql.com/bug.php?id=12108
From reading the comments, there appears to be some argument as to the validity of the bug. For what it’s worth, I found that the problem is more pronounced with more results. I found that it was non-existant (that is that the sum returned the correct value) with result sets with only a few rows. The problem seemed to get more severe based on more rows in the results. In my case, a result set with about 2000 rows in it had an error of on the order of three hours.
The work around is to modify the query slightly:
sum(TIME_TO_SEC(crf_call_duration)) as totalhours
This converts the time to essentially an integer which is then properly handled by the sum. You can then either work with the result as a count of seconds, or use the function SEC_TO_TIME to convert it back to a TIME value.
I hope this helps someone else. It cost me a number of hours…