CODEWITHSUNDEEP
mysqltimesum
Sebastian Farham
Sebastian Farham

Reputation: 825

Mysql SUM() TIME wrong format

I'm calculating the sum of two columns from the same table with SUM() but the end result is an integer (286676). I'm guessing it's milliseconds? How can I convert to TIME(00:00:00)?

database

id|hours_worked | hours_worked_wk2 |

hours_worked = 14:33:38 hours_worked_wk2 = 14:33:38

Query

SELECT *,SEC_TO_TIME(SUM(TIME_TO_SEC(ep.hours_worked)))+SEC_TO_TIME(SUM(TIME_TO_SEC(ep.hours_worked_wk2))) 
AS TotalHoursWorked 
FROM employeepayroll ep 
JOIN employees em ON ep.employee_id=em.employee_id 
JOIN payroll p ON ep.payroll_id=p.payroll_id 
JOIN payrolltaxes pt ON ep.payroll_id=pt.payroll_id 
WHERE ep.timesheet_status='Approved' AND p.pay_group='26' 
ORDER BY ep.payroll_id DESC

TotalHoursWorkd = 286676

Upvotes: 1

Views: 75

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179084

When you see it...

14:33:38 >>> 143,338 × 2 = 286,676

Yikes. The numbers in the times are being implicitly cast to integers.

You are adding the two values of SEC_TO_TIME(SUM(...))+SEC_TO_TIME(SUM(...)).

Instead, use SEC_TO_TIME(SUM(...) + SUM(...)).

Upvotes: 0

haMzox
haMzox

Reputation: 2109

Use the following formula. 

hours = cast(duration_in_milliseconds \ (60 * 60 * 1000) as int)  
mins = (duration_in_milliseconds \ (60 * 1000)) mod 60  
secs = (duration_in_milliseconds \ 1000) mod 60

Your query will look something like this: 

select cast(duration_in_milliseconds\(60*60*1000) as int)+':'((duration_in_milliseconds \ (60*1000)) mod 60;)+':'((duration_in_milliseconds \ 1000) mod 60) from something

Upvotes: 2

Related Questions