Mysql: How to Sum() a Timediff() on a Group

MySQL: How to SUM() a TIMEDIFF() on a group?

Use:

  SELECT t.user_id,       
         SEC_TO_TIME(SUM(TIME_TO_SEC(t.endtime) - TIME_TO_SEC(t.starttime))) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id

Steps:

1. Use TIME_TO_SEC to convert TIME to seconds for math operation
2. Sum the difference
3. Use SEC_TO_TIME to convert the seconds back to TIME

Based on the sample data, I'd have just suggested:

  SELECT t.user_id,       
         TIMEDIFF(MIN(t.startdate), MAX(t.enddate)) AS timediff
    FROM MYTABLE t
GROUP BY t.user_id   

NOTE: There is a bug in this code if you are using datetime.
TIME_TO_SEC only converts the time section so you end up with big
negatives if the clock goes past midnight. Use UNIX_TIMESTAMP instead
to do the sum. Also SEC_TO_TIME maxes out at values greater than
3020399 seconds e.g. SELECT TIME_TO_SEC(SEC_TO_TIME(3020400)); If you
see this value 838:59:59 you've reached the max and probably just need
to divide by 3600 to just show hours.

Mysql - sum time difference and group per hour

You can use hour():

select changedate, hour(changetime) changehour, 
    sum(timediff(currenttime,changetime)) as sum_timediff
from pins
group by changedate, changehour

MySQL - SUM of a group of time differences

Select  SEC_TO_TIME(SUM(TIME_TO_SEC(timediff(timeOut, timeIn)))) AS totalhours
FROM volHours 
WHERE username = 'skolcz'

If not then maybe:

Select  SEC_TO_TIME(SELECT SUM(TIME_TO_SEC(timediff(timeOut, timeIn))) 
FROM volHours 
WHERE username = 'skolcz') as totalhours

MySQL SUM of big TIMEDIFF

Thank you all for your answers.

As I thought, there is no way to do my thing in pure SQL and it will need some PHP treatment.

So, I'll go for a massive PHP treatment.

Thank you all !

Sum of date time difference in a mysql query

select, min(starttime), max(stoptime), 
SUM(TIME_TO_SEC(TIME_DIFF(stoptime - starttime))/3600) total_hours from mytable

MYSQL SUM durations within consecutive hours

E.g. - although, as written, this solution is exclusively for versions of MySQL prior to 8.0...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(person VARCHAR(12) NOT NULL
,start_time DATETIME NOT NULL
,end_time DATETIME NOT NULL
,PRIMARY KEY(person,start_time)
);

INSERT INTO my_table VALUES
('Alice','2020-02-27 20:00:00','2020-02-27 20:59:59'),
('Alice','2020-02-27 23:45:00','2020-02-27 23:59:59'),
('Alice','2020-02-28 00:00:00','2020-02-28 00:59:59'),
('Alice','2020-02-28 01:00:00','2020-02-28 01:59:59'),
('Bob','2020-02-27 23:45:00','2020-02-27 23:59:59'),
('Cindy','2020-02-28 02:00:00','2020-02-28 02:59:59'),
('Cindy','2020-02-28 03:00:00','2020-02-28 03:36:59');

SELECT person
     , MIN(start_time) start_time
     , MAX(end_time) end_time
     , SUM(TIME_TO_SEC(TIMEDIFF(end_time,start_time))) delta 
  FROM 
     ( SELECT x.*
            , CASE WHEN person = @prev_person 
                   THEN CASE WHEN start_time <= @prev_end_time + INTERVAL 1 HOUR 
                             THEN @i:=@i 
                             ELSE @i:=@i+1 END 
                   ELSE @i:=1 END i
            , @prev_person := person
            , @prev_end_time := end_time
         FROM my_table x
            , (SELECT @prev_person := null, @prev_end_time := null, @i:=0) vars 
        ORDER 
           BY person
            , start_time
     ) a
 GROUP  
    BY person,i;
+--------+---------------------+---------------------+-------+
| person | start_time          | end_time            | delta |
+--------+---------------------+---------------------+-------+
| Alice  | 2020-02-27 20:00:00 | 2020-02-27 20:59:59 |  3599 |
| Alice  | 2020-02-27 23:45:00 | 2020-02-28 01:59:59 |  8097 |
| Bob    | 2020-02-27 23:45:00 | 2020-02-27 23:59:59 |   899 |
| Cindy  | 2020-02-28 02:00:00 | 2020-02-28 03:36:59 |  5818 |
+--------+---------------------+---------------------+-------+

FWIW, I think rewriting the query this way renders it 'version agnostic', i.e. impervious to the fair accusation that the order of evaluation of elements is not guaranteed - but I might be wrong. Regardless, in MySQL 8.0+ the below can be rewritten with the extended functionality afforded by that version.

SELECT person
     , MIN(start_time) start_time
     , MAX(end_time) end_time
     , SUM(TIME_TO_SEC(TIMEDIFF(end_time,start_time))) delta 
  FROM 
  ( SELECT * FROM
     ( SELECT x.*
            , CASE WHEN person = @prev_person 
                   THEN CASE WHEN start_time <= @prev_end_time + INTERVAL 1 HOUR 
                             THEN @i:=@i 
                             ELSE @i:=@i+1 END 
                   ELSE @i:=1 END i
            , @prev_person := person
            , @prev_end_time := end_time
         FROM my_table x
            , (SELECT @prev_person := null, @prev_end_time := null, @i:=0) vars 
     ) k
      ORDER 
                BY person
            , start_time
     ) a
 GROUP  
    BY person,i;

MySQL - How to SUM times?

Try looking into UNIX_TIMESTAMP and SEC_TO_TIME.

You would sum up the differences between the timestamps, then use that value (would be in milliseconds) to get the time:

SELECT SEC_TO_TIME(time_milis / 1000)
FROM (
   SELECT SUM(UNIX_TIMESTAMP(date1) - UNIX_TIMESTAMP(date2)) as time_milis
   FROM table
)

---

Related Topics