Learning Objectives
By the end of this lesson you will be able to:
- Describe the six positional relationships between two date ranges
- Apply the standard overlap formula: A.start < B.end AND A.end > B.start
- Write the SQL WHERE clause that detects overlapping bookings
- Design test scenarios for hotel or room booking double-booking detection
- Distinguish overlapping ranges from contiguous (touching but non-overlapping) ranges
The Six Positional Relationships
Two date ranges (A and B) can be related in exactly six ways, based on Allen's Interval Relations simplified for date-only precision:
| Relationship | Description | Overlapping? |
|---|---|---|
| A completely before B | A ends before B starts | No |
| A meets B | A ends exactly where B starts | No (if half-open) |
| A overlaps B | A starts before B ends, A ends after B starts | Yes |
| A during B | A is entirely within B | Yes |
| A starts with B | Same start, A ends before B | Yes |
| A equals B | Identical range | Yes |
The mirror cases (B before A, B meets A, etc.) follow symmetrically. The key insight is that only two conditions produce NO overlap: A ends before B starts, or B ends before A starts.
The Standard Overlap Formula
The most reliable way to detect overlap is to negate the two non-overlap conditions:
NOT (A.end <= B.start OR B.end <= A.start)
By De Morgan's law, this is equivalent to:
A.start < B.end AND A.end > B.start
This formula works for both half-open and closed intervals when using < and > strictly (half-open semantics). For closed intervals, use <= and >=.
Memorize this formula. It is the single most commonly tested concept in date range logic interviews and the most commonly mis-implemented condition in production booking systems.
SQL WHERE Clause for Overlap Detection
To find all existing bookings that conflict with a new booking from :new_start to :new_end:
SELECT *
FROM bookings
WHERE resource_id = :room_id
AND NOT (check_out <= :new_start OR check_in >= :new_end)
Equivalently:
SELECT *
FROM bookings
WHERE resource_id = :room_id
AND check_in < :new_end
AND check_out > :new_start
If this query returns any rows, the new booking overlaps with an existing one and should be rejected.
Hotel/Room Booking Test Scenarios
For a room booking system, design these test cases:
| TC# | New booking | Existing booking | Expected |
|---|---|---|---|
| TC01 | June 10–15 | June 16–20 | No conflict |
| TC02 | June 10–15 | June 10–15 | Conflict (identical) |
| TC03 | June 10–15 | June 12–13 | Conflict (new contains existing) |
| TC04 | June 10–15 | June 8–12 | Conflict (partial overlap at start) |
| TC05 | June 10–15 | June 13–18 | Conflict (partial overlap at end) |
| TC06 | June 10–15 | June 15–20 | No conflict (half-open: new ends where existing starts) |
| TC07 | June 10–15 | June 5–10 | No conflict (half-open: existing ends where new starts) |
TC06 and TC07 are the diagnostic cases — they test the boundary between overlap and adjacency. If the system uses closed intervals instead of half-open, these two cases will incorrectly be reported as conflicts.
Contiguous vs Overlapping
"Contiguous" (also called "adjacent" or "touching") means two ranges share an endpoint but do not overlap. In half-open semantics:
- Booking A: June 10 to June 15 (exclusive)
- Booking B: June 15 to June 20 (exclusive)
These are contiguous — there is no gap between them, and no day belongs to both. This is the desired behavior for back-to-back hotel stays, consecutive work shifts, or sequential subscription periods.
A system that incorrectly classifies contiguous ranges as overlapping will reject valid back-to-back bookings, which is a usability bug. A system that incorrectly classifies genuinely overlapping ranges as contiguous will allow double-bookings, which is a correctness bug. The latter is the more severe defect.
Summary
Overlap detection is one of the most frequently mis-implemented features in date range management. The standard formula A.start < B.end AND A.end > B.start correctly identifies all six overlap conditions. SQL overlap detection uses the negated non-overlap form. The critical test cases are the boundary ones: adjacent bookings that share an endpoint but should not overlap. A system that passes TC06 and TC07 correctly has a properly implemented overlap check.