CODEWITHSUNDEEP
phpmysql
user827726
user827726

Reputation: 49

how to get the sum of the date difference?

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.

Upvotes: 1

Views: 177

Answers (2)

Johan
Johan

Reputation: 76547

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'

Upvotes: 1

Zack Marrapese
Zack Marrapese

Reputation: 12080

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.

Upvotes: 0

Related Questions