w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Mysql SUM two time and return total duration as days, hours, minutes

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)




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.