Temporal Overlap Detection Logic

Snippet

Temporal Overlap Detection Logic

Identifying overlapping intervals is a common expert challenge. The logic (StartA < EndB AND StartB < EndA) is the mathematically optimal way to detect any intersection between two time periods, avoiding complex nested CASE statements.

snippet.sql
sql
SELECT a.id, b.id
FROM Bookings a
JOIN Bookings b ON a.room_id = b.room_id
  AND a.id < b.id
WHERE a.start_time < b.end_time
  AND b.start_time < a.end_time;

Breakdown

AND a.id < b.id

Prevents comparing a row with itself and ensures each pair is only checked once.

WHERE a.start_time < b.end_time AND b.start_time < a.end_time

The 'Allen's Interval Algebra' inspired condition for detecting any overlap.

Previous snippet Next snippet

From your library

Temporal Overlap Detection Logic

Related