debz debz - 5 months ago 8
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

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;