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
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 | +-----+