The table is sample data about trips

the target is to calculate the time consumed for each category . EX: category A = 02:18:02

1st I changed the time stamp criteria in the csv file as YYYY/MM/DD HH:MM:SS to match with MYSQL, and removed the headers

I created a table in MYSQL Workbench as the following code

CREATE TABLE trip (
  start TIMESTAMP,
  end TIMESTAMP,
  category VARCHAR(6)
    );

Then to calculate the consumed time I coded as

SELECT category, SUM(TIMEDIFF(end, start))  as length
FROM trip
GROUP BY CATEGORY;

The result was solid numbers as A=34900 & B = 38000

SO I added a convert, Time function as following:

SELECT category, Convert(SUM(TIMEDIFF(end, start)), Time)  as length
FROM trip
GROUP BY category;

THE result was great with category A =03:49:00 , but unfortunately category B= NULL instead of 03:08:00

WHAT I'VE DONE WRONG , what is the different approach I should've done

CodePudding user response：

You'd calculate the length for each separate trip in seconds, get sum of the lengths per category then convert seconds to time:

SELECT category, SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, `end`, `start`))) as `length`
FROM trip
GROUP BY category;

If SUM() exceeds the limit for TIME datatype (838:59:59) then this MAXVALUE will be returned.

For the values which exceeds the limit for TIME value use

SELECT category,
       CONCAT_WS(':',
                 secs DIV (60 * 60),
                 LPAD(secs DIV 60 MOD 60, 2, 0),
                 LPAD(secs MOD 60, 2, 0)) AS `length`
FROM (
     SELECT category, SUM(TIMESTAMPDIFF(SECOND, `end`, `start`)) AS secs
     FROM trip
     GROUP BY category
     ) subquery
;

CodePudding user response：

You can do it as follows :

This is useful to Surpass MySQL's TIME value limit of 838:59:59

SELECT category, 
CONCAT(FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600),":",FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))600)/60),":",(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))600)`) as `length`
FROM trip
GROUP BY category;

This is to get time like 00:20:00 instead of 0:20:0

SELECT category, 
CONCAT(
    if(FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600) > 10, FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600), CONCAT('0',FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600)) ) ,
    ":",
    if(FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))600)/60) > 10, FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))600)/60), CONCAT('0', FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))600)/60) ) ),
    ":",
    if( (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )600)` > 10, (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )600)`, concat('0', (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )600)`))
    ) as `length`
FROM trip
GROUP BY category;