Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Summing time fields to get total time

Posted on 2011-02-17
Medium Priority
350 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +1

LVL 8

Expert Comment

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

LVL 59

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 59

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 59

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.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.