CODEWITHSUNDEEP
mysql
Lubomir Borisov
Lubomir Borisov

Reputation: 168

Mysql SUM two time and return total duration as days, hours, minutes

I want to select duration from several rows grouped by some id. The row contains time (00:04:25), (00:05:25), (12:04:25) (hours, minutes, seconds) etc. How I can sum these rows and return total duration as days, hours , minutes ? What I did is this (actually its not me I saw it in other post here on stackoverflow)

$sql = 'SELECT r.*,d.*,        CONCAT(
        FLOOR(HOUR(SEC_TO_TIME(SUM(TIME_TO_SEC(r.duration)))) / 24), "' . $this-     >_translator->translate(' days ') . '",
        MOD(HOUR(SEC_TO_TIME(SUM(TIME_TO_SEC(r.duration)))), 24), "' . $this->_translator->translate(' hours ') . '",
        MINUTE(SEC_TO_TIME(SUM(TIME_TO_SEC(r.duration)))), "' . $this->_translator->translate(' minutes ') . '") as total_duration, 
        ROUND(SUM(TIME_TO_SEC(r.duration)/3600)) as total_duration_hours FROM routes r LEFT JOIN drivers d ON d.id = r.driver_id GROUP BY r.driver_id';

This is working by not correctly. In r.* are stored also average speed , distance and for example this code returns 2 hours , 23 minutes when it should return 2 hours and 30 minutes. Please help. Thanks !

EDIT: Here is some data:

 Full texts     id  filename    distance    startRoute  endRoute    duration    avgSpeed    car_id  fromLocation    toLocation  driver_id
Edit Edit   Copy Copy   Delete Delete   748     GPSdata20140109073948   5.74    2014-01-09 07:40:23     2014-01-09 07:50:38     00:10:15    33.6    20  22, Ulhaus, Langerwehe  27, Königsbenden, Eschweiler   18
Edit Edit   Copy Copy   Delete Delete   754     GPSdata20140110182521   1.43    2014-01-10 18:25:26     2014-01-10 18:29:31     00:04:05    21.01   18  36, Grüner Weg, Aachen     3, Rehmplatz, Aachen    18
Edit Edit   Copy Copy   Delete Delete   758     GPSdata20140112145245   70.55   2014-01-12 14:54:05     2014-01-12 16:54:42     02:00:37    35.09   23  275, Hauptstraße, Langerwehe   , Pescher Weg, Cologne  18
Edit Edit   Copy Copy   Delete Delete   759     GPSdata20140113072802   6.17    2014-01-13 07:28:37     2014-01-13 07:37:02     00:08:25    43.98   19  13, Bahnhofstraße, Langerwehe  27, Königsbenden, Eschweiler   18

5.74 + 1.43 + 70.55 + 6.17 = 83.9 distance 33.6 + 21.01 + 35.09 + 43.98 = 33.42 average speed So distance / average speed = 2 hours and 30 minutes and i am getting 2 hours and 23 minutes. Where i am doing wrong ?

Upvotes: 0

Views: 3349

Answers (1)

Akhil
Akhil

Reputation: 479

You can use the following query to get the desired result: 

select CONCAT(FLOOR(time_in_sec/(60*60*24))," days " ,
FLOOR((time_in_sec/(60*60*24) - FLOOR(time_in_sec/(60*60*24)))*24)," hours ",
FLOOR(((time_in_sec/(60*60*24) - FLOOR(time_in_sec/(60*60*24)))*24 - 
FLOOR((time_in_sec/(60*60*24) - 
FLOOR(time_in_sec/(60*60*24)))*24))*60)," minutes" ) total_duration

FROM (
select SUM(TIME_TO_SEC(r.duration)) time_in_sec 
from routes r LEFT JOIN drivers d ON d.id = r.driver_id 
GROUP BY r.driver_id') T;

The logic of the query is as follows:
1. Sum total seconds and store it in variable time_in_sec. Use this result in from: 

FROM (
    select SUM(TIME_TO_SEC(r.duration)) time_in_sec 
    from routes r LEFT JOIN drivers d ON d.id = r.driver_id 
    GROUP BY r.driver_id') T;
  1. Rest is pure mathematics.
    a.)For days: Floor(time_in_sec/3600).
    b.)For Hours: Floor((time_in_sec/3600 - days)*24)
    c.)For minutes: Floor(((time_in_sec/3600 - days)*24 - hours)*60)

Upvotes: 1

Related Questions