SQL Date Range Overlap

DatabasesMiddbsql

The Question

How do you check for date range overlaps in SQL?

What a Strong Answer Covers

• The exact overlap formula.

Senior-Level Answer

Checking for date range overlap is a deceptively simple problem that many engineers get wrong by trying to enumerate overlap cases. The correct approach is to use the complement: two ranges do NOT overlap when one ends before the other starts. Negating that gives the universal overlap condition.

For ranges [A.start, A.end) and [B.start, B.end), the overlap condition is:

`A.start < B.end AND A.end > B.start`

This single expression handles all overlap geometries: partial overlap from either side, one range fully contained within the other, and identical ranges. The boundary behavior depends on whether your ranges are inclusive or exclusive on the endpoints. For closed intervals [A.start, A.end], use `<=` instead of `<`.

In SQL, this is expressed as a WHERE clause predicate. To find all bookings that overlap with a candidate booking ('2024-01-10', '2024-01-15'):

```sql SELECT * FROM bookings WHERE start_date < '2024-01-15' AND end_date > '2024-01-10'; ```

For self-joins (finding all overlapping pairs within a table):

```sql SELECT a.id, b.id FROM bookings a JOIN bookings b ON a.id < b.id AND a.start_date < b.end_date AND a.end_date > b.start_date; ```

The `a.id < b.id` condition prevents returning both (A,B) and (B,A).

Some databases provide native range types. PostgreSQL has the `tsrange` / `daterange` types with the `&&` overlap operator, which is both more readable and index-optimizable via GiST indexes. For high-volume overlap queries (scheduling systems, calendar applications), a plain B-tree index on start_date and end_date is insufficient — a GiST index on a range column, or application-level interval tree structures, are needed for sub-linear performance.

What Separates a 2/3 from a 3/3

Common Mistakes

• Trying to enumerate overlap cases (6+ conditions) instead of using the complement formula — error-prone and incomplete.
• Getting the inclusive/exclusive boundary wrong — using < vs <= without considering whether endpoints are included.
• Forgetting the a.id < b.id deduplication in self-join overlap queries, returning duplicate pairs.
• Not considering index strategy — a naive B-tree index on start_date alone will not efficiently prune queries with two-dimensional range predicates.

Follow-Up Questions

• Derive the overlap condition from first principles rather than just stating it. — Non-overlap means A ends before B starts (A.end <= B.start) OR B ends before A starts (B.end <= A.start). Negate with De Morgan's law.
• How would you efficiently index a table for frequent overlap queries in PostgreSQL? — Use daterange column type with a GiST index and the && operator — supports index scan on overlap queries.
• How does the answer change if your ranges are closed intervals [start, end] vs half-open [start, end)? — Closed intervals: use <= for endpoint comparisons. Half-open (common in time ranges): use strict < and >.
• Write a query to find all conflicting bookings for a specific room given a proposed start and end time. — Standard overlap predicate in WHERE clause against the bookings table filtered by room_id.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization