Calculate total time excluding overlapped time & breaks in MySQL

Question

There is a list of start time and end times from a select query. I need to find out the total time excluding the overlapping time and any breaks.

StartTime                   EndTime
2014-10-01 10:30:00.000     2014-10-01 12:00:00.000 -- 90 mins
2014-10-01 10:40:00.000     2014-10-01 12:00:00.000 --0 since its overlapped with previous
2014-10-01 10:42:00.000     2014-10-01 12:20:00.000 -- 20 mins excluding overlapped time
2014-10-01 10:40:00.000     2014-10-01 13:00:00.000 -- 40 mins
2014-10-01 10:44:00.000     2014-10-01 12:21:00.000 -- 0 previous ones have already covered this time range
2014-10-13 15:50:00.000     2014-10-13 16:00:00.000 -- 10 mins

So the total should be 160 mins in this case.

I have some ideas with lots of loops and if's. Just looking for some simple solutions if available.

What should the result actually look like? Just a single figure? — Strawberry, Nov 04 '14 at 17:19

score 2 · Accepted Answer · answered Nov 04 '14 at 19:06

This is a gaps-and-islands type of problem. Here is one way to tackle it

SELECT SUM(minutes) total
  FROM
(
  SELECT TIMESTAMPDIFF(MINUTE, MIN(starttime), MAX(endtime)) minutes
    FROM
  (
    SELECT starttime, endtime,
           @g := IF(@e BETWEEN starttime AND endtime OR endtime < @e, @g, @g + 1) g,
           @e := endtime         
      FROM table1 CROSS JOIN 
    (
      SELECT @g := 0, @e := NULL
    ) i
     ORDER BY starttime, endtime
  ) q
   GROUP BY g
) q

Output:

| TOTAL |
|-------|
|   160 |

Here is a SQLFiddle demo

Brillant! Finding this answer saved my day – Ratto Jan 11 '23 at 23:41 — Ratto, Jan 11 '23 at 23:41

score 0 · Answer 2 · answered Nov 04 '14 at 17:38

Here's a frankly rather poor and incomplete solution...

SELECT SUM(TIME_TO_SEC(n))/60
FROM 
 ( SELECT MAX(diff) n
     FROM  
        ( SELECT x.*
               , MAX(y.endtime) max_endtime
               , TIMEDIFF(MAX(y.endtime),x.starttime) diff 
            FROM my_table x 
            JOIN my_table y 
              ON y.endtime >= x.starttime 
             AND y.starttime <= x.endtime 
           GROUP 
              BY x.id
        ) a
    GROUP 
       BY max_endtime
 ) z;

In the unlikely event that no one beats me to it, I'll post something better later.

Calculate total time excluding overlapped time & breaks in MySQL

2 Answers2

Linked