GROUP BY function ret

start	end	category
2022:10:14 17:13:00	2022:10:14 17:19:00	A
2022:10:01 16:29:00	2022:10:01 16:49:00	B
2022:10:19 18:55:00	2022:10:19 19:03:00	A
2022:10:31 07:52:00	2022:10:31 07:58:00	A
2022:10:13 18:41:00	2022:10:13 19:26:00	B

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

Solution

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`))%3600)/60),":",(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)%60) 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`))%3600)/60) > 10, FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)/60), CONCAT('0', FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)/60) ) ),
    ":",
    if( (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )%3600)%60 > 10, (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )%3600)%60, concat('0', (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )%3600)%60))
    ) as `length`
FROM trip
GROUP BY category;