sql / expert
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
1
2
3
4
5
6
SELECT a.id, b.idFROM Bookings aJOIN Bookings b ON a.room_id = b.room_idAND a.id < b.idWHERE a.start_time < b.end_timeAND b.start_time < a.end_time;
Breakdown
1
AND a.id < b.id
Prevents comparing a row with itself and ensures each pair is only checked once.
2
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.