Solved

Summing time fields to get total time

Posted on 2011-02-17
8
343 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

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

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 51

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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Closing Comment

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now