Question 21662 in Mysql Question posted in
The official MySQL documentation can be found here.

GROUP BY function ret – Mysql

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

Tags:

2

Answers


  1. 0

    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
;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search