Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Example 
(
    ID	INT,
    StartTime	TIME,
    EndTime	TIME
);

INSERT INTO Example (ID, StartTime, EndTime) VALUES ('123', '5:30:00', '5:35:00');
INSERT INTO Example (ID, StartTime, EndTime) VALUES ('123', '5:36:00', '5:40:00');
INSERT INTO Example (ID, StartTime, EndTime) VALUES ('123', '5:36:00', '5:40:00');
INSERT INTO Example (ID, StartTime, EndTime) VALUES ('123', '5:38:00', '5:42:00');

Select * from Example;

--find the timespent per record
SELECT id, 
   DATE_PART('hour', EndTime - StartTime) * 60 +
              DATE_PART('minute', EndTime - StartTime) as TimeSpent
FROM Example;

--Simply summing the timespent gives us 17min which is far from 11
SELECT id, 
   SUM(DATE_PART('hour', EndTime - StartTime) * 60 +
              DATE_PART('minute', EndTime - StartTime)) as TotalTimeSpent
FROM Example
GROUP BY id;

--what if we just looked at distinct rows
SELECT distinct id, StartTime, EndTime,
   DATE_PART('hour', EndTime - StartTime) * 60 +
              DATE_PART('minute', EndTime - StartTime) as TimeSpent
FROM Example;

--sum distinct gives us 13min..closer
SELECT id,
   sum(TimeSpent) as TotalTimeSpent
FROM (
SELECT distinct id, StartTime, EndTime,
   DATE_PART('hour', EndTime - StartTime) * 60 +
              DATE_PART('minute', EndTime - StartTime) as TimeSpent
FROM Example
) t
GROUP BY id;


CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1




id
starttime
endtime


123
05:30:00
05:35:00

123
05:36:00
05:40:00

123
05:36:00
05:40:00

123
05:38:00
05:42:00



SELECT 4




id
timespent


123
5

123
4

123
4

123
4



SELECT 4




id
totaltimespent


123
17



SELECT 1




id
starttime
endtime
timespent


123
05:30:00
05:35:00
5

123
05:38:00
05:42:00
4

123
05:36:00
05:40:00
4



SELECT 3




id
totaltimespent


123
13



SELECT 1


--find overlaps
SELECT id, StartTime, EndTime,
  CASE
    WHEN LAG(EndTime) OVER () > StartTime THEN 0
    ELSE 1
  END AS ovl
FROM Example;


id
starttime
endtime
ovl


123
05:30:00
05:35:00
1

123
05:36:00
05:40:00
1

123
05:36:00
05:40:00
0

123
05:38:00
05:42:00
0



SELECT 4


--So, whenever there's a new interval, there's a 1 in the ovl (overlap) column.
--Next, we take the cumulative SUM of those 1's as follows:

SELECT 
  *,
  SUM(ovl) OVER (ORDER BY t1.StartTime ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
FROM
(
  SELECT id, StartTime, EndTime,
  CASE
    WHEN LAG(EndTime) OVER () > StartTime THEN 0
    ELSE 1
  END AS ovl
  FROM Example
) AS t1
ORDER BY EndTime, id



id
starttime
endtime
ovl
s


123
05:30:00
05:35:00
1
1

123
05:36:00
05:40:00
1
2

123
05:36:00
05:40:00
0
2

123
05:38:00
05:42:00
0
2



SELECT 4


--we have now "split out", and have a way of distinguishing between, our intervals 
-- each interval has a different value of s - 1..5.

SELECT 
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(StartTime) OVER (PARTITION BY s ORDER BY StartTime, EndTime) AS min_start, 
  MAX(EndTime) OVER (PARTITION BY s ORDER BY StartTime, EndTime) AS max_end,
  s
FROM
(
  SELECT   *,
  SUM(ovl) OVER (ORDER BY t1.StartTime ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
  FROM
  (
  SELECT id, StartTime, EndTime,
  CASE
    WHEN LAG(EndTime) OVER () > StartTime THEN 0
    ELSE 1
  END AS ovl
  FROM Example
  ) AS t1
  ORDER BY id, EndTime
)AS t2
ORDER BY s, rn ASC, min_start;

--rn = 1 is always our desired record


rn
min_start
max_end
s


1
05:30:00
05:35:00
1

1
05:36:00
05:40:00
2

2
05:36:00
05:40:00
2

3
05:36:00
05:42:00
2



SELECT 4


SELECT 
  ROW_NUMBER() OVER () AS "Interval No.", 
  ' Start time: ' AS " ",
  t3.min_start AS "Interval start" , 
  ' End time: ' AS " ",
  t3.max_end AS "Interval stop", 
  ' Duration: ' AS " ",
  (t3.max_end - t3.min_start) AS "Duration"
FROM
(
SELECT 
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(StartTime) OVER (PARTITION BY s ORDER BY StartTime, EndTime desc) AS min_start, 
  MAX(EndTime) OVER (PARTITION BY s ORDER BY StartTime desc, EndTime) AS max_end,
  s
FROM
(
  SELECT   *,
  SUM(ovl) OVER (ORDER BY t1.StartTime ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
  FROM
  (
  SELECT id, StartTime, EndTime,
  CASE
    WHEN LAG(EndTime) OVER () > StartTime THEN 0
    ELSE 1
  END AS ovl
  FROM Example
  ) AS t1
  ORDER BY id, EndTime
)AS t2
ORDER BY s, rn ASC, min_start
) AS t3 
WHERE t3.rn = 1;


Interval No.
 
Interval start
 
Interval stop
 
Duration


1
 Start time:
05:30:00
 End time:
05:35:00
 Duration:
00:05:00

2
 Start time:
05:36:00
 End time:
05:42:00
 Duration:
00:06:00



SELECT 2


SELECT id, sum(t3.max_end - t3.min_start) as TotalTime
FROM
(
SELECT 
  id,
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(StartTime) OVER (PARTITION BY s ORDER BY StartTime, EndTime desc) AS min_start, 
  MAX(EndTime) OVER (PARTITION BY s ORDER BY StartTime desc, EndTime) AS max_end,
  s
FROM
(
  SELECT   *,
  SUM(ovl) OVER (ORDER BY t1.StartTime ASC ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW) AS s
  FROM
  (
  SELECT id, StartTime, EndTime,
  CASE
    WHEN LAG(EndTime) OVER () > StartTime THEN 0
    ELSE 1
  END AS ovl
  FROM Example
  ) AS t1
  ORDER BY id, EndTime
)AS t2
ORDER BY s, rn ASC, min_start  
) t3
WHERE t3.rn = 1
GROUP BY id;



id
totaltime


123
00:11:00



SELECT 1
id	starttime	endtime
123	05:30:00	05:35:00
123	05:36:00	05:40:00
123	05:36:00	05:40:00
123	05:38:00	05:42:00
Interval No.		Interval start		Interval stop		Duration
1	Start time:	05:30:00	End time:	05:35:00	Duration:	00:05:00
2	Start time:	05:36:00	End time:	05:42:00	Duration:	00:06:00