Solved

# Summing time fields to get total time

Posted on 2011-02-17
Medium Priority
352 Views
Using MySQL.

I have a field called 'duration' that has data in the format  '1899-12-31 00:08:55'

I ned to sum up the TIME portion of this field and show th result as HH:MM:SS, what would the sql synax be fo this??
0
Question by:BrianFord
• 3
• 2
• 2
• +1

LVL 8

Expert Comment

ID: 34919840
Is the duration field datetime or varchar?
0

LVL 61

Expert Comment

ID: 34919867
maybe this:

SEC_TO_TIME(
hour(mydt)*60*60 +
minute(mydt)*60 +
second(mtdt)
)

check all date & time functions here

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour
0

LVL 61

Expert Comment

ID: 34919881
something like this:

select
SEC_TO_TIME(sum(
hour(mydt)*60*60 +
minute(mydt)*60 +
second(mtdt)
)
)
from my table where ...
0

LVL 4

Accepted Solution

incerc earned 2000 total points
ID: 34919889
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIME(duration))))

I explain below:

TIME(duration) = 00:08:55
TIME_TO_SEC('00:08:55') = this converted in seconds = 8*60 + 55
SUM(..) = sum all those seconds
SEC_TO_TIME = convert back into time format hh:mm:ss

0

Author Closing Comment

ID: 34920060
Perfect thanks very much :)
0

LVL 61

Expert Comment

ID: 34920095
but my solution is basically the same one that you posted :(

TIME(mydt) = hour(mydt)*60*60 + minute(mydt)*60 + second(mtdt)
0

Author Comment

ID: 34920120
My appologies,

I actually mis-read your solution, if there is a way for me to split the points at this stage I'm happy to do so, soryy :(
0

LVL 4

Expert Comment

ID: 34920818
I'm sorry HainKurt, I didn't want to steal your solution, I was unaware of it, we just posted at the same time (you faster, it took me more time to explain my solution).
I'd be happy to share my points with you, if possible :)

0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.