Detect Overlapping Date Ranges

Find rows whose date or timestamp intervals intersect by checking that neither range ends before the other begins — using the `OVERLAPS` operator in PostgreSQL or a manual comparison in other engines.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find room bookings on the same room whose date ranges overlap

The self-join pairs every booking against every other booking on the same room. a.id < b.id prevents duplicates (each pair appears once, not twice). Booking 1 (Mar 1–5) and booking 2 (Mar 4–8) overlap: Mar 1 < Mar 8 AND Mar 4 < Mar 5, so both conditions hold. Booking 2 (Mar 4–8) and booking 3 (Mar 8–10) do not overlap: Mar 8 < Mar 8 is false — the ranges touch at March 8 but do not share any interior point. This is half-open interval semantics: a check-out date is the first day a room is available, not the last day it is occupied. Booking 4 is in room 102 and is never paired with the room 101 bookings because the join requires the same room. PostgreSQL's OVERLAPS operator encapsulates the same half-open test in a single expression.

MySQL MariaDB SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE bookings (
  id INT,
  room VARCHAR(10),
  check_in DATE,
  check_out DATE
);

INSERT INTO
  bookings
VALUES
  (1, '101', '2024-03-01', '2024-03-05'),
  (2, '101', '2024-03-04', '2024-03-08'),
  (3, '101', '2024-03-08', '2024-03-10'),
  (4, '102', '2024-03-01', '2024-03-06');

Validated query

SQL

SELECT
  a.id AS booking_a,
  b.id AS booking_b,
  a.room
FROM
  bookings a
  JOIN bookings b ON a.room = b.room
  AND a.id < b.id
WHERE
  a.check_in < b.check_out
  AND b.check_in < a.check_out
ORDER BY
  a.id,
  b.id;

Expected result

booking_a	booking_b	room
1	2	101

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE bookings (
  id INT,
  room VARCHAR(10),
  check_in DATE,
  check_out DATE
);

INSERT INTO
  bookings
VALUES
  (1, '101', '2024-03-01', '2024-03-05'),
  (2, '101', '2024-03-04', '2024-03-08'),
  (3, '101', '2024-03-08', '2024-03-10'),
  (4, '102', '2024-03-01', '2024-03-06');

Validated query

SQL

SELECT
  a.id AS booking_a,
  b.id AS booking_b,
  a.room
FROM
  bookings a
  JOIN bookings b ON a.room = b.room
  AND a.id < b.id
WHERE
  (a.check_in, a.check_out) OVERLAPS (b.check_in, b.check_out)
ORDER BY
  a.id,
  b.id;

Expected result

booking_a	booking_b	room
1	2	101

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE bookings (
  id INT,
  room VARCHAR(10),
  check_in TEXT,
  check_out TEXT
);

INSERT INTO
  bookings
VALUES
  (1, '101', '2024-03-01', '2024-03-05'),
  (2, '101', '2024-03-04', '2024-03-08'),
  (3, '101', '2024-03-08', '2024-03-10'),
  (4, '102', '2024-03-01', '2024-03-06');

Validated query

SQL

SELECT
  a.id AS booking_a,
  b.id AS booking_b,
  a.room
FROM
  bookings a
  JOIN bookings b ON a.room = b.room
  AND a.id < b.id
WHERE
  a.check_in < b.check_out
  AND b.check_in < a.check_out
ORDER BY
  a.id,
  b.id;

Expected result

booking_a	booking_b	room
1	2	101

PostgreSQL uses the OVERLAPS operator. All other supported engines use the equivalent two-condition comparison. Results are identical.

Useful when

Where this command helps.

finding conflicting hotel or meeting room bookings where two reservations overlap on the same resource
validating that a new event does not overlap any existing scheduled events before inserting

Explanation

What the command is doing.

Two date ranges [s1, e1) and [s2, e2) overlap when s1 < e2 AND s2 < e1. Both conditions must hold: the first range must start before the second ends, and the second must start before the first ends. Checking only one direction is a common bug that misses cases. This pattern is used to detect booking conflicts, scheduling collisions, and any scenario involving intervals. PostgreSQL supports the SQL-standard (s1, e1) OVERLAPS (s2, e2) operator, which implements the half-open interval test s1 < e2 AND s2 < e1 — two ranges that touch at exactly one boundary point do not overlap. MySQL, MariaDB, SQL Server, and SQLite do not support the OVERLAPS operator; write the two-condition comparison directly. For closed-interval semantics (endpoints count as overlapping), use s1 <= e2 AND s2 <= e1 instead.

Detect Overlapping Date Ranges

Find room bookings on the same room whose date ranges overlap

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.