Search code examples
phpmysqlsumgroup-concat

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.

Solution

  • 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;