Select all ID's that have overlapping timestamp values

Question

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

`Is there a way to retrieve all Ids that have an overlapping start and end time in Sql?` yes, of course, start here: https://stackoverflow.com/questions/3269434/whats-the-most-efficient-way-to-test-if-two-ranges-overlap — Iłya Bursov, Jun 10 '22 at 20:01
Do you only care about overlaps with the same id, or would you want 1 and 2 returned if they overlapped with each other? — EdmCoff, Jun 10 '22 at 20:09
I only care about overlaps with the same id. In this case, 0 even though there is some overlap between id 1 and id 0. — Shockercj, Jun 10 '22 at 20:11
Yes, Cross Join the table with itslef and compare the B.Starttime between A.StartTime and A.EndTime OR B.EndTime between A.StartTime and A.EndTime Return a.id, B.ID But I hope it's not a "BIG" table or the cross join will take some time unless you have other limits. — xQbert, Jun 10 '22 at 20:11
Is there no unique id per row? In addition to the logic suggested above, you might need use a `row_number` or something to ensure you don't incorrectly match the row with itself and get a false overlap match. — EdmCoff, Jun 10 '22 at 20:16
For one particular row, it'll always only have one id. That ID however can show up multiple times in the table with a different start / end time. — Shockercj, Jun 10 '22 at 20:21

xQbert · Accepted Answer · 2022-06-10T20:46:56.980

0

fiddle:

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

edited Jun 10 '22 at 20:46

answered Jun 10 '22 at 20:26

xQbert

34,733
2
41
62

I only want to compare the timestamps on rows that happen to have the same id. – Shockercj Jun 10 '22 at 20:37
Updated now we have an equality check on the cross join as well and distinct ensures we only see each ID once. Note the 'SRC' common table expression was just a build of your data. – xQbert Jun 10 '22 at 20:50

score 0 · Answer 2 · answered Jun 10 '22 at 20:33

There are multiple ways of doing this, but the basic idea would be something like:

WITH mytable AS
 (
  SELECT row_number() OVER () rid, id, starttime, endtime
  FROM myrealtable
 )
SELECT distinct id
FROM mytable mt1
WHERE exists
 (SELECT *
  FROM mytable mt2
  WHERE mt2.id = mt1.id 
   AND mt2.starttime >= mt1.starttime and mt2.starttime < mt1.endtime)
OR exists 
(SELECT *
 FROM mytable mt2
 WHERE mt2.id = mt1.id 
  AND mt2.endtime > mt1.starttime and mt2.endtime <= mt1.endtime)

Or:

WITH mytable AS
 (
  SELECT row_number() OVER () rid, id, starttime, endtime
  FROM myrealtable
 )
SELECT distinct mt1.id
FROM mytable mt1 INNER JOIN mytable mt2
  ON mt2.id = mt1.id and mt2.rid <> mt1.rid
WHERE (mt2.starttime >= mt1.starttime and mt2.starttime < mt1.endtime)
 OR (mt2.endtime > mt1.starttime and mt2.endtime <= mt1.endtime)

The CTE is just to get a unique id (rid) from row_number to ensure we don't match the row with itself. You might have an alternate way of doing this (e.g. maybe you know all the columns can't be the same).

If you want to handle the times inclusively, you could use BETWEEN to make the conditions a little simpler, but I assume 10:00-11:00 and 11:00-11:30 should not be considered overlaps.

I listed two different ways since I think the first method (with EXISTS) is very human-readable and easy to understand what it's doing. The second method (with a JOIN) is probably better (shorter and might end up optimized better) and should make sense if you understand the first example.

Here's a DB fiddle showing it.

Select all ID's that have overlapping timestamp values

2 Answers2