By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Intervalle (
time_start DATETIME,
time_end DATETIME
)
INSERT INTO Intervalle
VALUES
('2024-01-01 12:30', '2024-01-01 13:30'),
('2024-01-01 12:40', '2024-01-01 13:40'),
('2024-01-01 12:45', '2024-01-01 13:45'),
('2024-01-01 13:36', '2024-01-01 14:36'),
('2024-01-01 13:50', '2024-01-01 14:50'),
('2024-01-01 14:30', '2024-01-01 15:30'),
('2024-01-01 15:35', '2024-01-01 16:35'),
('2024-01-01 16:30', '2024-01-01 17:30'),
('2024-01-01 17:30', '2024-01-01 18:30')
Records: 9 Duplicates: 0 Warnings: 0
WITH RECURSIVE SelectedIntervals AS (
SELECT I2.*
FROM (
SELECT I.*
FROM Intervalle I
ORDER BY I.time_start
LIMIT 1
) I2
UNION ALL
SELECT I2.*
FROM SelectedIntervals S
, LATERAL (
SELECT I.time_start, I.time_end
FROM Intervalle I
WHERE I.time_start >= S.time_start + INTERVAL 1 HOUR
ORDER BY I.time_start
LIMIT 1
) I2
)
SELECT *
FROM SelectedIntervals
| time_start | time_end |
|---|---|
| 2024-01-01 12:30:00 | 2024-01-01 13:30:00 |
| 2024-01-01 13:36:00 | 2024-01-01 14:36:00 |
| 2024-01-01 15:35:00 | 2024-01-01 16:35:00 |
| 2024-01-01 17:30:00 | 2024-01-01 18:30:00 |
WITH RECURSIVE TaggedIntervals AS (
SELECT
I2.*,
1 AS IsGap,
1 AS Island,
I2.time_start AS ref_time
FROM (
SELECT I.*
FROM Intervalle I
ORDER BY I.time_start
LIMIT 1
) I2
UNION ALL
SELECT
I2.*,
T.Island + I2.IsGap AS Island,
CASE WHEN I2.IsGap = 1 THEN I2.time_start ELSE T.ref_time END AS ref_time
FROM TaggedIntervals T
, LATERAL (
SELECT
I.*,
CASE WHEN I.time_start >= T.ref_time + INTERVAL 1 HOUR
THEN 1 ELSE 0 END AS IsGap
FROM Intervalle I
WHERE I.time_start > T.time_start
ORDER BY I.time_start
LIMIT 1
) I2
)
SELECT *
FROM TaggedIntervals
| time_start | time_end | IsGap | Island | ref_time |
|---|---|---|---|---|
| 2024-01-01 12:30:00 | 2024-01-01 13:30:00 | 1 | 1 | 2024-01-01 12:30:00 |
| 2024-01-01 12:40:00 | 2024-01-01 13:40:00 | 0 | 1 | 2024-01-01 12:30:00 |
| 2024-01-01 12:45:00 | 2024-01-01 13:45:00 | 0 | 1 | 2024-01-01 12:30:00 |
| 2024-01-01 13:36:00 | 2024-01-01 14:36:00 | 1 | 2 | 2024-01-01 13:36:00 |
| 2024-01-01 13:50:00 | 2024-01-01 14:50:00 | 0 | 2 | 2024-01-01 13:36:00 |
| 2024-01-01 14:30:00 | 2024-01-01 15:30:00 | 0 | 2 | 2024-01-01 13:36:00 |
| 2024-01-01 15:35:00 | 2024-01-01 16:35:00 | 1 | 3 | 2024-01-01 15:35:00 |
| 2024-01-01 16:30:00 | 2024-01-01 17:30:00 | 0 | 3 | 2024-01-01 15:35:00 |
| 2024-01-01 17:30:00 | 2024-01-01 18:30:00 | 1 | 4 | 2024-01-01 17:30:00 |
WITH RECURSIVE NumberedIntervals AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY time_start) AS RowNum
FROM Intervalle
),
TaggedIntervals AS (
SELECT
N.*,
1 AS IsGap,
1 as Island,
N.time_start AS ref_time
FROM NumberedIntervals N
WHERE N.RowNum = 1
UNION ALL
SELECT
N.*,
G.IsGap,
T.Island + G.IsGap AS Island,
CASE WHEN G.IsGap = 1 THEN N.time_start ELSE T.ref_time END AS ref_time
FROM TaggedIntervals T
JOIN NumberedIntervals N ON N.RowNum = T.RowNum + 1
, LATERAL (
SELECT CASE WHEN N.time_start >= T.ref_time + INTERVAL 1 HOUR
THEN 1 ELSE 0 END AS IsGap
) G
)
SELECT T.*
FROM TaggedIntervals T
/* WHERE T.IsGap = 1 */
| time_start | time_end | RowNum | IsGap | Island | ref_time |
|---|---|---|---|---|---|
| 2024-01-01 12:30:00 | 2024-01-01 13:30:00 | 1 | 1 | 1 | 2024-01-01 12:30:00 |
| 2024-01-01 12:40:00 | 2024-01-01 13:40:00 | 2 | 0 | 1 | 2024-01-01 12:30:00 |
| 2024-01-01 12:45:00 | 2024-01-01 13:45:00 | 3 | 0 | 1 | 2024-01-01 12:30:00 |
| 2024-01-01 13:36:00 | 2024-01-01 14:36:00 | 4 | 1 | 2 | 2024-01-01 13:36:00 |
| 2024-01-01 13:50:00 | 2024-01-01 14:50:00 | 5 | 0 | 2 | 2024-01-01 13:36:00 |
| 2024-01-01 14:30:00 | 2024-01-01 15:30:00 | 6 | 0 | 2 | 2024-01-01 13:36:00 |
| 2024-01-01 15:35:00 | 2024-01-01 16:35:00 | 7 | 1 | 3 | 2024-01-01 15:35:00 |
| 2024-01-01 16:30:00 | 2024-01-01 17:30:00 | 8 | 0 | 3 | 2024-01-01 15:35:00 |
| 2024-01-01 17:30:00 | 2024-01-01 18:30:00 | 9 | 1 | 4 | 2024-01-01 17:30:00 |