debz debz - 2 years ago 84
PHP Question

How can I get the correct sum() of time fields with exact value in HH:MM:SS format

I used a query similar to the one below at PHPMyAdmin :

SELECT id, name, group_concat(timecomplete) AS 'Timelist', sum(timetaken) AS 'Total Time'
FROM dailysummarylog

This is what was generated on my query:

ID | Name | Timelist | Total Time
1 | Employee 1 |06:35:15,00:59:54,01:24:45 | 81914
2 | Employee 2 |02:44:42,00:59:36,00:15:10 | 31888

Problem #1: I used sum() on Timelist to get Total Time but it's not showing the correct total. The Total Time when I try to compute it, should have been as follows:

  • 1: 8 hours, 59 minutes, 54 seconds

  • 2: 3 hours, 59 minutes, 28 seconds

Problem #2: When I try to store it into another table using PHP, only the first value was shown for Timelist and Total Time for employee 2 showed "00:00:00". For employee 1, it showed "08:19:14"

Additional Info:
Timelist and Total Time are both set to 'Time' type.

Answer Source

answer for problem 1 use SEC_TO_TIME and TIME_TO_SEC FUNCTIONS

SELECT id, name, group_concat(timecomplete) AS 'Timelist', SEC_TO_TIME(sum(TIME_TO_SEC((timetaken))) AS 'Total Time'
FROM dailysummarylog group by name;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download