Given this table, I’m trying to select all of ids that have an overlapping start_time and end_time grouped by ID. In this case, the table has multiple Ids that may or may not have multiple entries. (In this case, id’s 1 and 2 don’t have multiple rows, whereas 0 does.) Is there a way to retrieve all Ids that have an overlapping start and end time in Sql?
Example: Given this table, design some query to yield the subsequent table.
id | start time | end time |
---|---|---|
0 | 2022-06-10 12:44:55 | 2022-06-10 12:46:55 |
1 | 2022-06-10 12:47:55 | 2022-06-10 12:48:55 |
2 | 2022-06-10 12:49:00 | 2022-06-10 12:50:00 |
0 | 2022-06-10 12:45:55 | 2022-06-10 12:48:55 |
Id’s with timestamp overlaps |
---|
0 |
Advertisement
Answer
x
With SRC AS (
SELECT 0 id, '2022-06-10 12:44:55' start_time, '2022-06-10 12:46:55' end_time FROM DUAL UNION ALL
SELECT 1, '2022-06-10 12:47:55', '2022-06-10 12:48:55' FROM DUAL UNION ALL
SELECT 2, '2022-06-10 12:49:00', '2022-06-10 12:50:00' FROM DUAL UNION ALL
SELECT 0, '2022-06-10 12:45:55', '2022-06-10 12:48:55' FROM DUAL )
SELECT distinct A.id
FROM SRC A
CROSS JOIN SRC B
on (B.start_time between A.start_time and A.end_time
OR B.end_time between A.start_time and A.end_time)
AND (A.start_time <> B.start_Time
OR B.end_time <> B.end_time)
AND A.id = B.id
Giving us:
+-----+
| Aid |
+-----+
| 0 |
+-----+