Solved

Summing time fields to get total time

Posted on 2011-02-17
8
349 Views
Last Modified: 2012-05-11
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 34919840
Is the duration field datetime or varchar?
0
 
LVL 56

Expert Comment

by:HainKurt
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 56

Expert Comment

by:HainKurt
ID: 34919881
something like this:

select
SEC_TO_TIME(sum(
hour(mydt)*60*60 +
minute(mydt)*60 +
second(mtdt)
)
)
from my table where ...
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Accepted Solution

by:
incerc earned 500 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

More about this: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
0
 

Author Closing Comment

by:BrianFord
ID: 34920060
Perfect thanks very much :)
0
 
LVL 56

Expert Comment

by:HainKurt
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

by:BrianFord
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

by:incerc
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 :)

BrianFord, glad to help! :)
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question