5

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

Example time graph

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')
  • You write Exclude overlapping ranges (8:00 - 10:00). But later you write (8:00 to 12:00 + 14:00 to 18:00) - so overlapping was not excluded, it was calculated once... where is the truth? – Akina Jul 17 '18 at 13:48
  • I completely don't understand what you mean? Each range is used only once in the example – Łukasz Jul 18 '18 at 14:38
  • "Exclude" == "remove all" != "exclude double-counting" == "remove duplicates". I think... – Akina Jul 19 '18 at 4:32
3

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

  • 1
    What will your query do if there are two ranges, from 1 to 4 and 2 to 5? – ypercubeᵀᴹ Jan 23 '16 at 12:58
  • The query won't work as required. Thanks for the heads up. Will post an edit shortly. – Serge Jan 23 '16 at 13:21
1

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.

  • Have you tried the revised solution above? It's less verbose but probably more efficient than a cursor. You may need to adjust column names to match your table definition. – Serge Jan 26 '16 at 0:06
  • Thanks @Serge, I tried the solution, so I marked is as usefull, however because of my project's specification the cursor method is a little bit more useful. To be honest - both of solutions has props and cons, I mark your solution as correct. – Łukasz Jan 27 '16 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.