OVERLAPS в SQL: как проверить пересечение двух периодов

OVERLAPS в SQL отвечает на очень практичный вопрос:

«Пересекаются ли два периода времени?»

Это нужно постоянно: при бронированиях, расписаниях, сменах, подписках, аренде, доставках, встречах и любых задачах, где есть начало и конец периода.

Например:

• можно ли забронировать комнату с 10:00 до 12:00;
• не пересекаются ли две смены сотрудника;
• активна ли подписка в нужный период;
• есть ли конфликт между двумя заказами доставки;
• не заняли ли один и тот же ресурс два раза.

Без OVERLAPS пришлось бы вручную сравнивать начало и конец одного периода с началом и концом другого. С OVERLAPS запрос читается намного ближе к человеческой фразе: «период A пересекается с периодом B».

Простая идея

Допустим, у нас есть два периода:

Period	Start	End
A	2024-03-01	2024-03-10
B	2024-03-08	2024-03-15

Они пересекаются, потому что оба включают даты с 8 по 10 марта.

А теперь другая пара:

Period	Start	End
A	2024-03-01	2024-03-10
B	2024-03-10	2024-03-20

На первый взгляд кажется, что они «касаются» 10 марта. Но в PostgreSQL такие периоды не считаются пересекающимися.

Почему? Потому что периоды обычно трактуются как полуоткрытые интервалы:

[start, end)

Это значит:

• начало входит в период;
• конец не входит в период.

Период с 1 по 10 марта длится до начала 10 марта, но не включает сам следующий период, который стартует 10 марта.

Такой подход очень удобен для расписаний. Смена с 09:00 до 17:00 не конфликтует со сменой с 17:00 до 01:00. Они идут встык, но не пересекаются.

Базовый синтаксис OVERLAPS

В PostgreSQL оператор OVERLAPS записывается между двумя парами дат или времени.

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

Результат:

does_overlap
true

Периоды пересекаются.

Теперь пример со смежными периодами:

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

Результат:

does_overlap
false

Конец первого периода совпал с началом второго. Для OVERLAPS это не конфликт.

Как читать выражение с OVERLAPS

Запись выглядит немного необычно, потому что это инфиксный оператор: он стоит между двумя выражениями.

(period_start, period_end) OVERLAPS (other_start, other_end)

Читать можно так:

«Период от period_start до period_end пересекается с периодом от other_start до other_end?»

Например:

SELECT
  (TIMESTAMP '2024-03-01 10:00', TIMESTAMP '2024-03-01 12:00')
  OVERLAPS
  (TIMESTAMP '2024-03-01 11:30', TIMESTAMP '2024-03-01 13:00') AS has_conflict;

Результат будет true, потому что период с 11:30 до 12:00 общий для обоих интервалов.

Что можно передавать в OVERLAPS

Обычно каждый период задаётся двумя значениями:

(start_time, end_time)

PostgreSQL поддерживает работу с датами и временем:

• date;
• time;
• timestamp;
• timestamp with time zone;
• пара «момент плюс длительность».

Например, можно задать период не концом, а длительностью:

SELECT
  (TIMESTAMP '2024-03-01 10:00', INTERVAL '2 hours')
  OVERLAPS
  (TIMESTAMP '2024-03-01 11:00', INTERVAL '1 hour') AS does_overlap;

Первый период начинается в 10:00 и длится 2 часа, то есть заканчивается в 12:00.
Второй начинается в 11:00 и длится 1 час, то есть заканчивается в 12:00.

Они пересекаются, поэтому результат будет true.

Если начало больше конца

Иногда данные приезжают неидеальными: начало и конец могут оказаться перепутаны местами.

В PostgreSQL OVERLAPS умеет с этим справляться: если начало периода больше конца, PostgreSQL воспринимает меньшую дату как начало, а большую — как конец.

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

Этот пример всё равно вернёт true, потому что первый период фактически будет прочитан как период с 1 по 10 марта.

Но полагаться на это как на норму не стоит. В реальных таблицах лучше хранить данные аккуратно: начало в одной колонке, конец — в другой, и желательно проверять это ограничением.

Пример: таблица бронирований

Создадим таблицу бронирований комнат.

CREATE TABLE bookings (
  id bigint PRIMARY KEY,
  room_id bigint NOT NULL,
  user_id bigint NOT NULL,
  starts_at timestamp NOT NULL,
  ends_at timestamp NOT NULL
);

В ней каждая строка — одно бронирование:

id	room_id	starts_at	ends_at
1	10	2024-03-01 09:00	2024-03-01 11:00
2	10	2024-03-01 12:00	2024-03-01 14:00
3	20	2024-03-01 10:00	2024-03-01 13:00

Допустим, пользователь хочет забронировать комнату 10 с 10:30 до 12:30. Нужно проверить, есть ли конфликт.

SELECT
  id,
  room_id,
  starts_at,
  ends_at
FROM bookings
WHERE room_id = 10
  AND (starts_at, ends_at)
      OVERLAPS
      (TIMESTAMP '2024-03-01 10:30', TIMESTAMP '2024-03-01 12:30');

Запрос вернёт бронирования, которые пересекаются с новым периодом.

В нашем примере конфликт будет и с бронированием 09:00-11:00, и с бронированием 12:00-14:00, потому что новый период задевает оба.

Пример: соседние бронирования не конфликтуют

Теперь проверим ситуацию «встык».

Есть бронирование с 09:00 до 11:00. Пользователь хочет забронировать с 11:00 до 12:00.

SELECT
  (TIMESTAMP '2024-03-01 09:00', TIMESTAMP '2024-03-01 11:00')
  OVERLAPS
  (TIMESTAMP '2024-03-01 11:00', TIMESTAMP '2024-03-01 12:00') AS has_conflict;

Результат:

has_conflict
false

Это правильное поведение для большинства расписаний: одно бронирование закончилось, другое сразу началось.

Если в вашей бизнес-логике нужно закладывать уборку, перерыв или буфер между бронированиями, это лучше добавлять отдельно. Например, расширить проверяемый период на 15 минут.

SELECT
  id,
  room_id,
  starts_at,
  ends_at
FROM bookings
WHERE room_id = 10
  AND (starts_at, ends_at)
      OVERLAPS
      (
        TIMESTAMP '2024-03-01 11:00' - INTERVAL '15 minutes',
        TIMESTAMP '2024-03-01 12:00'
      );

Так мы говорим: «перед новым бронированием нужен запас 15 минут».

Пример: найти все конфликтующие пары

Иногда нужно найти не конфликт с новым периодом, а все пересекающиеся периоды внутри самой таблицы.

Например, найти комнаты, которые случайно забронированы дважды на одно и то же время.

Для этого используют соединение таблицы с самой собой.

SELECT
  b1.id AS first_booking_id,
  b2.id AS second_booking_id,
  b1.room_id
FROM bookings b1
JOIN bookings b2
  ON b1.room_id = b2.room_id
 AND b1.id < b2.id
 AND (b1.starts_at, b1.ends_at)
     OVERLAPS
     (b2.starts_at, b2.ends_at)
ORDER BY b1.room_id, b1.id, b2.id;

Здесь есть важное условие:

b1.id < b2.id

Оно нужно сразу по двум причинам.

Во-первых, оно не даёт строке сравниваться с самой собой.

Во-вторых, оно убирает дубли. Без этого пара 1-2 и пара 2-1 попали бы в результат как два разных совпадения, хотя это один и тот же конфликт.

Пример: пересечение подписок

Допустим, есть таблица подписок:

CREATE TABLE subscriptions (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  plan_name text NOT NULL,
  starts_at date NOT NULL,
  ends_at date NOT NULL
);

Нужно найти пользователей, у которых пересекаются две подписки.

SELECT
  s1.user_id,
  s1.id AS first_subscription_id,
  s2.id AS second_subscription_id
FROM subscriptions s1
JOIN subscriptions s2
  ON s1.user_id = s2.user_id
 AND s1.id < s2.id
 AND (s1.starts_at, s1.ends_at)
     OVERLAPS
     (s2.starts_at, s2.ends_at)
ORDER BY s1.user_id;

Такой запрос помогает найти ошибки в данных: например, когда пользователю случайно выдали два активных тарифа на один и тот же период.

Эквивалент OVERLAPS через обычные сравнения

OVERLAPS — удобная короткая запись. Но за ней стоит простое логическое правило.

Два полуоткрытых периода пересекаются, если:

a_start < b_end
AND b_start < a_end

То есть:

• начало первого периода раньше конца второго;
• начало второго периода раньше конца первого.

Например, проверку бронирований можно написать без OVERLAPS:

SELECT
  id,
  room_id,
  starts_at,
  ends_at
FROM bookings
WHERE room_id = 10
  AND starts_at < TIMESTAMP '2024-03-01 12:30'
  AND TIMESTAMP '2024-03-01 10:30' < ends_at;

Это условие делает то же самое: ищет существующие бронирования, которые пересекаются с новым периодом 10:30-12:30.

Почему в ручном условии нужен строгий знак <

Самая частая ошибка — написать <= вместо <.

Вот правильное условие для поведения как у OVERLAPS:

a_start < b_end
AND b_start < a_end

А вот это уже другое правило:

a_start <= b_end
AND b_start <= a_end

Разница проявляется на соседних периодах.

Период A:

09:00 - 11:00

Период B:

11:00 - 12:00

Со строгим < пересечения нет.
С <= пересечение будет, потому что конец одного периода равен началу другого.

Иногда <= действительно нужен. Например, если вы проверяете не бронирования, а непрерывное покрытие периода, где касание границ важно.

Но если вы хотите повторить поведение OVERLAPS, используйте строгий <.

Когда ручное условие лучше OVERLAPS

OVERLAPS хорошо читается, но есть ситуации, где ручная форма удобнее.

Например:

• вы пишете запрос для MySQL, где нет оператора OVERLAPS;
• нужно явно контролировать включение и исключение границ;
• нужно проще объяснить оптимизатору работу с индексами;
• команда привыкла к условию start < other_end AND other_start < end;
• нужно перенести запрос между несколькими СУБД.

Тот же поиск конфликтующих бронирований можно записать так:

SELECT
  b1.id AS first_booking_id,
  b2.id AS second_booking_id,
  b1.room_id
FROM bookings b1
JOIN bookings b2
  ON b1.room_id = b2.room_id
 AND b1.id < b2.id
 AND b1.starts_at < b2.ends_at
 AND b2.starts_at < b1.ends_at
ORDER BY b1.room_id, b1.id, b2.id;

Эта запись длиннее, зато работает почти везде и явно показывает правило пересечения.

OVERLAPS и NULL

Если в одном из значений периода окажется NULL, результат может стать NULL, то есть неизвестным.

Например:

SELECT
  (DATE '2024-03-01', NULL)
  OVERLAPS
  (DATE '2024-03-05', DATE '2024-03-10') AS does_overlap;

SQL не может уверенно сказать, пересекаются периоды или нет, потому что конец первого периода неизвестен.

В WHERE значение NULL ведёт себя не как true, поэтому такая строка не пройдёт фильтр.

На практике это важно для открытых периодов. Например, у подписки может быть ends_at = NULL, если она ещё активна и даты окончания нет.

В таком случае нужно заранее решить, как трактовать NULL.

Например, можно заменить неизвестный конец на очень далёкую дату:

SELECT
  id,
  user_id,
  starts_at,
  ends_at
FROM subscriptions
WHERE (
    starts_at,
    coalesce(ends_at, DATE '9999-12-31')
  )
  OVERLAPS
  (DATE '2024-03-01', DATE '2024-04-01');

Такой запрос считает подписки без даты окончания бесконечно активными до условно далёкого будущего.

Главное — не прятать это решение. Если NULL означает «пока не закончилась», используйте COALESCE осознанно и одинаково во всех отчётах.

Важная проверка качества данных

Для периодов почти всегда полезно добавить ограничение: конец должен быть больше начала.

Например, для бронирований:

ALTER TABLE bookings
ADD CONSTRAINT bookings_valid_period
CHECK (starts_at < ends_at);

Так база не позволит вставить бронирование, где конец раньше начала или равен началу.

Иногда период нулевой длины допустим, тогда можно использовать <=:

ALTER TABLE bookings
ADD CONSTRAINT bookings_valid_period
CHECK (starts_at <= ends_at);

Но для расписаний, бронирований и смен чаще всего нужен именно вариант starts_at < ends_at.

Range-типы в PostgreSQL

В PostgreSQL есть более мощная альтернатива для работы с периодами — диапазонные типы.

Например:

• daterange — диапазон дат;
• tsrange — диапазон timestamp;
• tstzrange — диапазон timestamp with time zone;
• int4range — диапазон целых чисел.

Для пересечения диапазонов используется оператор &&.

SELECT
  id,
  room_id,
  starts_at,
  ends_at
FROM bookings
WHERE tsrange(starts_at, ends_at)
  && tsrange(TIMESTAMP '2024-03-01 10:30', TIMESTAMP '2024-03-01 12:30');

Эта запись означает:

«Диапазон бронирования пересекается с новым диапазоном».

По смыслу это очень похоже на OVERLAPS, но у range-типов больше возможностей.

Почему range-типы могут быть лучше

OVERLAPS удобен для разовой проверки в запросе.

Но если вы строите серьёзную систему бронирований, смен или расписаний, одних запросов мало. Нужно не просто найти конфликт, а не допустить его появления.

Вот здесь range-типы особенно полезны.

В PostgreSQL можно создать ограничение, которое запретит пересекающиеся периоды на уровне базы данных.

Например, запретим бронировать одну комнату на пересекающиеся периоды.

Сначала может понадобиться расширение btree_gist, чтобы использовать обычное равенство по room_id вместе с GiST-индексом.

CREATE EXTENSION IF NOT EXISTS btree_gist;

Теперь создадим ограничение.

ALTER TABLE bookings
ADD CONSTRAINT no_booking_overlap
EXCLUDE USING gist (
  room_id WITH =,
  tsrange(starts_at, ends_at) WITH &&
);

Теперь база сама не позволит вставить второе бронирование той же комнаты на пересекающийся период.

Это очень сильная защита. Она работает даже тогда, когда два пользователя пытаются забронировать комнату почти одновременно. Проверка живёт не в приложении, а в базе данных.

Почему это важно для гонок

Представьте приложение бронирования.

Первый пользователь проверил комнату: свободна.
Второй пользователь почти одновременно проверил комнату: тоже свободна.
Оба нажали «Забронировать».

Если проверка конфликта есть только в коде приложения, можно поймать гонку: оба запроса успели пройти проверку и оба вставили бронирование.

Ограничение EXCLUDE в базе решает эту проблему надёжнее: даже если приложение ошиблось или два запроса пришли одновременно, база не даст сохранить пересекающиеся периоды.

Поэтому для критичных расписаний лучше не ограничиваться запросом на поиск конфликтов. Лучше добавить гарантию на уровне таблицы.

OVERLAPS или range-типы

Можно использовать простое правило.

OVERLAPS хорош, когда нужно быстро проверить пересечение в запросе:

SELECT
  id
FROM bookings
WHERE (starts_at, ends_at)
  OVERLAPS
  (TIMESTAMP '2024-03-01 10:30', TIMESTAMP '2024-03-01 12:30');

Range-типы хороши, когда вы часто работаете с интервалами и хотите больше возможностей:

SELECT
  id
FROM bookings
WHERE tsrange(starts_at, ends_at)
  && tsrange(TIMESTAMP '2024-03-01 10:30', TIMESTAMP '2024-03-01 12:30');

А EXCLUDE нужен, когда пересечения нужно не просто находить, а запрещать.

Что с MySQL и ClickHouse

В MySQL оператора OVERLAPS нет. Поэтому там используют ручное условие:

SELECT
  b1.id AS first_booking_id,
  b2.id AS second_booking_id
FROM bookings b1
JOIN bookings b2
  ON b1.room_id = b2.room_id
 AND b1.id < b2.id
 AND b1.starts_at < b2.ends_at
 AND b2.starts_at < b1.ends_at;

В ClickHouse тоже обычно используют ручное сравнение дат:

SELECT
  b1.id AS first_booking_id,
  b2.id AS second_booking_id
FROM bookings b1
INNER JOIN bookings b2
  ON b1.room_id = b2.room_id
WHERE b1.id < b2.id
  AND b1.starts_at < b2.ends_at
  AND b2.starts_at < b1.ends_at;

Главное запомнить не конкретный синтаксис, а правило:

a_start < b_end
AND b_start < a_end

Это универсальная формула пересечения двух полуоткрытых периодов.

Частая ошибка: забыть про одну комнату, одного пользователя или один ресурс

Допустим, мы ищем пересечения бронирований.

Плохой запрос:

SELECT
  b1.id AS first_booking_id,
  b2.id AS second_booking_id
FROM bookings b1
JOIN bookings b2
  ON b1.id < b2.id
 AND (b1.starts_at, b1.ends_at)
     OVERLAPS
     (b2.starts_at, b2.ends_at);

Он найдёт все пересечения по времени вообще. Но если одно бронирование относится к комнате 10, а другое к комнате 20, это не конфликт.

Правильнее добавить условие на ресурс:

SELECT
  b1.id AS first_booking_id,
  b2.id AS second_booking_id,
  b1.room_id
FROM bookings b1
JOIN bookings b2
  ON b1.room_id = b2.room_id
 AND b1.id < b2.id
 AND (b1.starts_at, b1.ends_at)
     OVERLAPS
     (b2.starts_at, b2.ends_at);

Для смен это может быть employee_id.
Для подписок — user_id.
Для доставки — courier_id или vehicle_id.

Антипример простой: два человека могут работать в одно и то же время, но один и тот же человек не должен быть поставлен в две смены одновременно.

Частая ошибка: не договориться о границах

Самый тонкий вопрос в периодах — что делать с границами.

Периоды:

09:00 - 11:00
11:00 - 13:00

Это конфликт или нет?

Для OVERLAPS — нет.

Но в вашей предметной области может быть иначе. Например:

• врачу нужен перерыв между пациентами;
• переговорку нужно проветрить;
• машине нужна подготовка перед следующей арендой;
• курьер не может мгновенно перейти к следующей доставке.

В таких случаях не надо менять смысл OVERLAPS в голове. Лучше явно добавить буфер.

SELECT
  id,
  room_id,
  starts_at,
  ends_at
FROM bookings
WHERE room_id = 10
  AND (starts_at, ends_at + INTERVAL '10 minutes')
      OVERLAPS
      (TIMESTAMP '2024-03-01 11:00', TIMESTAMP '2024-03-01 12:00');

Теперь существующее бронирование как будто длится на 10 минут дольше, и новое бронирование встык уже может считаться конфликтом.

Главное

OVERLAPS в SQL проверяет, пересекаются ли два периода времени.

В PostgreSQL он записывается так:

(start_a, end_a) OVERLAPS (start_b, end_b)

Если периоды имеют общую часть, результат будет true. Если один период заканчивается ровно там, где начинается другой, результат будет false.

Такое поведение соответствует полуоткрытым интервалам:

[start, end)

Для разовой проверки в PostgreSQL удобно использовать OVERLAPS.

Для переносимости между СУБД используйте ручное условие:

a_start < b_end
AND b_start < a_end

Для серьёзных систем бронирований, расписаний и смен в PostgreSQL стоит посмотреть в сторону range-типов, оператора && и ограничений EXCLUDE. Они позволяют не только искать пересечения, но и запрещать их на уровне базы данных.

Короткое правило:

Разовая проверка — OVERLAPS. Переносимый SQL — ручное сравнение дат. Жёсткая защита от конфликтов — range-типы и EXCLUDE.