how to get the sum of the date difference?

Question

my data in table is like this

ds_start_time -> 09:00:00 , ds_end_time-> 09:30:00
ds_start_time -> 09:00:00 , ds_end_time-> 10:30:00
ds_start_time -> 10:00:00 , ds_end_time-> 10:30:00
ds_start_time -> 09:30:00 , ds_end_time-> 10:30:00

i want the difference of date and sum that difference for that i put this in query

SELECT SUM(TIMEDIFF(ds_end_time,ds_start_time)) 
FROM www
WHERE abc='123'........

but at the end i am getting 2058800.000000 . From this how can i get total hrs mins and sec. Or any other method .Plz suggest.

USe TIME_TO_SEC and SEC_TO_TIME like in this answer: http://stackoverflow.com/questions/4102480/mysql-how-to-sum-a-timediff-on-a-group — Jacob, Jul 14 '11 at 13:08

score 1 · Accepted Answer · answered Jul 14 '11 at 13:24

It's easy to do this in MySQL if you know how:

SELECT SEC_TO_TIME(SUM
    (TIME_TO_SEC(ds_end_time) - TIME_TO_SEC(ds_start_time))
                 ) AS timediff     
 FROM www 
 WHERE abc = '123'

Note that if there are many rows that have abc = '123' then you will probably get double counts, in that cause you migth want to try:

SELECT SEC_TO_TIME(
    (MAX(TIME_TO_SEC(ds_end_time)) - MIN(TIME_TO_SEC(ds_start_time)))
                 ) AS timediff     
 FROM www 
 WHERE abc = '123'

@user827726 try this as Johan said this will the best i have seen — jeni, Jul 14 '11 at 13:27

score 0 · Answer 2 · answered Jul 14 '11 at 13:10

This would be much easier to do using PHP instead of SQL. Consider getting the actual times out and then looping through them.

$total = 0;
foreach ($results as $val) {
    $total += strtotime($val['ds_end_time']) - strtotime($val['ds_start_time']);
}

Then with $total, you have the total milliseconds to do what you want with.

how to get the sum of the date difference?

2 Answers2