CODEWITHSUNDEEP
sqlsqlitedatetime
mokiliii Lo
mokiliii Lo

Reputation: 637

Find total duration (in minutes) between multiple rows with overlaps

I have the following table:

start         end
('19:48:43', '21:12:43')
('21:16:36', '21:17:36')
('21:19:22', '21:46:22')
('21:22:41', '22:03:41')
('21:23:29', '22:04:29')

And I need to know the total time (in minutes) between start and end taking into account overlaps. Given the above table, the computation would look like this:

total = 84 + 1 + 45 = 130 minutes

I'm using SQLite. So far I haven't been able to arrive at any solution.

Upvotes: 0

Views: 428

Answers (1)

forpas
forpas

Reputation: 164089

With a LEFT join of 2 copies of the table to the table itself and aggregation:

WITH cte AS (
  SELECT DISTINCT 
        COALESCE(MIN(ts.start), t.start) start,
        COALESCE(MAX(te.end), t.end) end   
  FROM tablename t
  LEFT JOIN tablename ts ON t.start BETWEEN ts.start AND ts.end
  LEFT JOIN tablename te ON t.end BETWEEN te.start AND te.end
  GROUP BY t.start, t.end
)
SELECT SUM(strftime('%s', end) - strftime('%s', start)) / 60 total 
FROM cte

Or simpler:

WITH cte AS (
  SELECT DISTINCT 
        COALESCE(
          (SELECT MIN(t2.start) 
           FROM tablename t2 
           WHERE t1.start BETWEEN t2.start AND t2.end), t1.start) start,
        COALESCE(
          (SELECT MAX(t2.end) 
           FROM tablename t2 
           WHERE t1.end BETWEEN t2.start AND t2.end), t1.start) end   
  FROM tablename t1
)
SELECT SUM(strftime('%s', end) - strftime('%s', start)) / 60 total 
FROM cte

See the demo.
Results:

total
130

Upvotes: 2

Related Questions