The SQL OVERLAPS Operator: Detecting Period Intersections and Conflicts

OVERLAPS is a standard SQL infix operator that answers one question: do two time periods intersect? It saves you from manually comparing four dates and is perfect for finding conflicts in bookings, shifts, and subscriptions.

Basic syntax

Each period is a parenthesized pair: (start, end). The expression returns true when the intervals share any point in time.

SELECT (DATE '2024-03-01', DATE '2024-03-10')
    OVERLAPS (DATE '2024-03-08', DATE '2024-03-15')  AS does_overlap;  -- true

SELECT (DATE '2024-03-01', DATE '2024-03-10')
    OVERLAPS (DATE '2024-03-10', DATE '2024-03-20')  AS does_overlap;  -- false

The second example returns false, and that is the key thing to internalize: the bounds are treated as a half-open interval [start, end). One period's end touching another's start does not count as an overlap, which is exactly what you want for back-to-back shifts.

• Each operand is a (start, end) pair.
• It accepts date, timestamp, time, and even a (moment, interval) pair.
• If start is greater than end, PostgreSQL silently swaps them.

Finding booking conflicts

Suppose reservations are attached to orders. We want any order whose period intersects a new request for March 5 to 8:

SELECT o.id, o.user_id
FROM orders o
WHERE (o.created_at, o.created_at + INTERVAL '3 days')
   OVERLAPS (TIMESTAMP '2024-03-05', TIMESTAMP '2024-03-08');

To find every pair of overlapping periods within one table, self-join and use o1.id < o2.id to drop duplicates and self-comparisons:

SELECT o1.id AS first_id, o2.id AS second_id
FROM orders o1
JOIN orders o2
  ON o1.user_id = o2.user_id
 AND o1.id < o2.id
 AND (o1.created_at, o1.created_at + INTERVAL '1 hour')
  OVERLAPS (o2.created_at, o2.created_at + INTERVAL '1 hour');

The date-comparison equivalent

OVERLAPS is syntactic sugar over the classic condition. Two periods intersect if and only if:

-- a_start < b_end AND b_start < a_end
WHERE a_start < b_end
  AND b_start < a_end

This form is essential when OVERLAPS is awkward or unavailable — for instance in MySQL, which has no such operator. The same conflict search:

SELECT o1.id, o2.id
FROM orders o1
JOIN orders o2
  ON o1.user_id = o2.user_id
 AND o1.id < o2.id
 AND o1.created_at < o2.created_at + INTERVAL '1 hour'
 AND o2.created_at < o1.created_at + INTERVAL '1 hour';

Gotcha: a common mistake is writing <= instead of <. With strict <, adjacent periods (end = start) are not treated as a conflict, matching OVERLAPS exactly. If you actually want touching to count as an overlap (say, for continuous coverage), use <= — but then the behavior diverges from OVERLAPS.

Range types as an alternative

PostgreSQL has dedicated range types (tsrange, daterange, int4range) and an overlap operator &&. It reads better and, unlike OVERLAPS, can be indexed with GiST.

SELECT o.id
FROM orders o
WHERE tsrange(o.created_at, o.created_at + INTERVAL '3 days')
   && tsrange(TIMESTAMP '2024-03-05', TIMESTAMP '2024-03-08');

The big win is exclusion constraints: the database itself guarantees that periods never overlap, with no application-level race conditions. For example, you can forbid booking one employee for two simultaneous shifts:

ALTER TABLE employees
  ADD COLUMN shift tsrange;

ALTER TABLE employees
  ADD CONSTRAINT no_shift_conflict
  EXCLUDE USING gist (id WITH =, shift WITH &&);

Any attempt to insert an overlapping shift for the same id fails at the database level. ClickHouse has neither OVERLAPS nor range types, so there you fall back to the manual a_start < b_end AND b_start < a_end condition.

Bottom line: for a one-off check in WHERE reach for OVERLAPS; for portability use the manual comparison; and for indexing plus hard integrity guarantees use range types with EXCLUDE.