MySQL: sum time ranges exluding overlapping ones

Question

I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office:

Query has to:

Exclude overlapping ranges (8:00 - 10:00)
Exclude "missing" part (12:00 - 14:00)

Expected result in this case is 8:00 (8:00 to 12:00 + 14:00 to 18:00)

Example table structure:

DAY        | TIME_ENTER | TIME_EXIT
2016-01-01 | 08:00      | 10:00
2016-01-01 | 08:00      | 12:00
2016-01-01 | 14:00      | 18:00

Expected result:

DAY        | TOTAL
2016-01-01 | 08:00

Table structure:

CREATE TABLE Registry
 (`Date` DATE,
  `Enter` TIME,
  `Exit` TIME);

Example inserts

INSERT INTO Registry
VALUES
 ('2016-01-01', '08:00', '09:00'),
 ('2016-01-01', '08:00', '12:00'),
 ('2016-01-01', '14:00', '18:00')

Serge · Accepted Answer · 2016-01-23T13:26:09.043

Original

You want to sum up all time ranges, except those that fit inside other time ranges on the same day:

SELECT DATE_FORMAT(DAY, '%Y-%m-%d')
        ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(
            SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER))
          )), '%H:%i') AS Total
FROM Table1 AS t
WHERE NOT EXISTS (
  SELECT *
  FROM Table1
  WHERE DAY = t.DAY
  AND TIME_ENTER <= t.TIME_ENTER AND TIME_EXIT >= t.TIME_EXIT
  # not the same row; if you have an ID column, use it instead
  # eg AND NOT ID = t.ID
  AND NOT (TIME_ENTER = t.TIME_ENTER AND TIME_EXIT = t.TIME_EXIT)
)
GROUP BY DAY;

SqlFiddle

Edit: the above solution won't always work, as pointed out by ypercube. The following solution should cover all scenarios.

Revised solution

SELECT DATE_FORMAT(DAY, '%Y-%m-%d')
        ,TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(
            SUM(TIMEDIFF(TIME_EXIT,TIME_ENTER))
          )), '%H:%i') AS Total
FROM (
  SELECT   DISTINCT t.DAY
        ,(SELECT MIN(TIME_ENTER) FROM Table1
          WHERE TIME_EXIT BETWEEN t.TIME_ENTER AND t.TIME_EXIT) AS TIME_ENTER
        ,(SELECT MAX(TIME_EXIT) FROM Table1
          WHERE TIME_ENTER BETWEEN t.TIME_ENTER AND t.TIME_EXIT) AS TIME_EXIT
  FROM Table1 t
  ) AS o
GROUP BY DAY;

SqlFiddle

score 1 · Answer 2 · answered Jan 23 '16 at 13:45

Finally - found the solution. Quite long and complicated, however comments helps to understand "What's going on":

DELIMITER //

/* Function returns the number of seconds, not formatted TIME neither DATE */
CREATE FUNCTION SumWorkTime (registry_date date) returns INT
BEGIN

DECLARE time_min DATETIME DEFAULT NULL;
DECLARE time_max DATETIME DEFAULT NULL;

/* Just for the moment */
DECLARE tmpEnter TIME;
DECLARE tmpExit TIME;

/* Just for the purpose of "next day" */
DECLARE curEnter DATETIME;
DECLARE curExit DATETIME;

DECLARE work_time INT DEFAULT 0;

DECLARE FinishHim INT DEFAULT 0;
DECLARE eCursor CURSOR FOR SELECT `Enter`, `Exit` FROM Registry WHERE `Date` = registry_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET FinishHim = 1;

OPEN eCursor;
  REPEAT
    FETCH eCursor INTO tmpEnter, tmpExit;

  /* We're probably not going back in time... */
    SET curEnter = CONCAT(registry_date, ' ', tmpEnter);
  IF tmpExit > tmpEnter THEN 
    SET curExit = CONCAT(registry_date, ' ', tmpExit);
  ELSE 
    SET curExit = CONCAT(DATE_SUB(registry_date, INTERVAL -1 DAY), ' ', tmpExit);
  END IF;

    /* It's first definition */
    IF time_min IS NULL AND time_max IS NULL THEN
      SET work_time = work_time + (UNIX_TIMESTAMP(curExit) - UNIX_TIMESTAMP(curEnter));
    END IF;

    /* If new Enter is least than last ones */
    IF time_min IS NULL THEN
      SET time_min  = curEnter;
    ELSEIF time_min > curEnter THEN
      SET work_time = work_time + (UNIX_TIMESTAMP(time_min) - UNIX_TIMESTAMP(curEnter));
      SET time_min  = curEnter;
    END IF;

    /* If new Exit time greater than last ones */
    IF time_max IS NULL THEN
      SET time_max = curExit;
    ELSEIF time_max < curExit THEN
      SET work_time = work_time + (UNIX_TIMESTAMP(curExit) - UNIX_TIMESTAMP(time_max));
      IF (curEnter > time_max) THEN
        SET work_time = work_time - (UNIX_TIMESTAMP(curEnter) - UNIX_TIMESTAMP(time_max));
      END IF;
      SET time_max = curExit;
    END IF;
  UNTIL finishHim END REPEAT;
CLOSE eCursor;

RETURN work_time;

END;
//

How to execute?

SELECT SumWorkTime('2016-01-01');

I think that's enough for my project, however if you find it useful - you can extend / optimize this solution.

MySQL: sum time ranges exluding overlapping ones

2 Answers2