how to SUM time in HH:MM

[justinhn]

Hey Guys,

I'm having trouble summing up a list of times stored in a MySQL database. I have a column of attribute TIME, with name "duration" of which i want totalled. The tricky bit is that I need the result displayed in HH:MM format.

My query works 80% of the time, but on certain occasions, it returns nothing. For example sometimes the query returns 35:30, however If i add a row of 04:30 to the table, my total becomes blank.

$query="SELECT TIME_FORMAT((SUM(duration)),'%H:%i') FROM timecard;
$total=mysql_query($query);

Any suggestions of what is causing this, and how to fix it?

Thanks,
- Justin

🙂

[mrhappiness]

SELECT
  SEC_TO_TIME(SUM(TIME_TO_SEC(duration)))
FROM 
  timecard

more useful functions are to be found at http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

[justinhn]

Thanks,

However that returns a result in HH:MM:SS format. Any suggestions on how to achieve HH:MM format?

[mrhappiness]

Originally posted by mrhappiness
more useful functions are to be found at http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

TIME_FORMAT for example is one of those functions 😉