Интервалы дат

Что такое интервал

Интервал — это отрезок дат с началом и концом. В учебной базе Команда у назначения есть from_date и to_date, у проекта — start_date и end_date. Оба конца включительны: день начала и день конца входят в отрезок.

На учебной базе интернет-магазина таких отрезков нет: у заказа одна дата. Здесь берём назначения Анны Ким.

SELECT p.name AS project, a.from_date, a.to_date
FROM assignments a
JOIN projects p ON p.id = a.project_id
JOIN employees e ON e.id = a.employee_id
WHERE e.name = 'Анна Ким'
ORDER BY a.from_date, p.name;

Альфа длится с 1 января по 30 июня включительно. 30 июня ещё Альфа, 1 июля — уже нет.

Точка внутри отрезка

Дата попадает в интервал, если она не раньше начала и не позже конца:

дата >= начало AND дата <= конец

Тимур Волков вышел 1 марта 2024. Какие проекты в этот день уже шли:

SELECT p.name AS project, p.start_date, p.end_date
FROM employees e
JOIN projects p
  ON e.hired_on >= p.start_date
 AND e.hired_on <= p.end_date
WHERE e.name = 'Тимур Волков'
ORDER BY p.name;

Если написать hired_on > start_date, Бета пропадёт: день старта перестанет считаться попаданием.

Два отрезка пересекаются

Два интервала пересекаются, если есть хотя бы один общий день. Для включительных границ это одно условие:

a.from_date <= b.to_date
AND b.from_date <= a.to_date

Альфа Анны: 1 января — 30 июня. Бета: 1 марта — 31 декабря. 1 января не позже 31 декабря, 1 марта не позже 30 июня — общий кусок есть.

SELECT
  p1.name AS project_a,
  p2.name AS project_b
FROM assignments a
JOIN assignments b
  ON a.employee_id = b.employee_id
 AND a.project_id < b.project_id
 AND a.from_date <= b.to_date
 AND b.from_date <= a.to_date
JOIN projects p1 ON p1.id = a.project_id
JOIN projects p2 ON p2.id = b.project_id
JOIN employees e ON e.id = a.employee_id
WHERE e.name = 'Анна Ким'
ORDER BY project_a, project_b;

a.project_id < b.project_id убирает пару с собой и дубль «Бета–Альфа», если уже есть «Альфа–Бета».

У Софьи Громовой два назначения без общего дня: Бета до 31 мая, Гамма с 1 июля. Июнь пустой — условие ложно, строк нет.

SELECT p.name AS project, a.from_date, a.to_date
FROM assignments a
JOIN projects p ON p.id = a.project_id
JOIN employees e ON e.id = a.employee_id
WHERE e.name = 'Софья Громова'
ORDER BY a.from_date;

Стык в один день — это уже пересечение. Если бы Гамма начиналась 31 мая, условие b.from_date <= a.to_date стало бы истинным.

Мария Лосева на Бете до 31 августа, Тимур Волков с 1 сентября. 31 августа < 1 сентября, общего дня нет.

Границы пересечения

Общий кусок начинается в более поздний из стартов и кончается в более ранний из концов:

GREATEST(a.from_date, b.from_date)  -- начало общего
LEAST(a.to_date, b.to_date)          -- конец общего

Альфа и Бета Анны: позже из стартов — 1 марта, раньше из концов — 30 июня. Пересечение: 1 марта — 30 июня включительно.

Если начало общего позже конца, пересечения нет. То же условие, что в прошлом разделе: GREATEST(старты) <= LEAST(концы).

SELECT
  p1.name AS project_a,
  p2.name AS project_b,
  GREATEST(a.from_date, b.from_date) AS overlap_start,
  LEAST(a.to_date, b.to_date) AS overlap_end
FROM assignments a
JOIN assignments b
  ON a.employee_id = b.employee_id
 AND a.project_id < b.project_id
 AND a.from_date <= b.to_date
 AND b.from_date <= a.to_date
JOIN projects p1 ON p1.id = a.project_id
JOIN projects p2 ON p2.id = b.project_id
JOIN employees e ON e.id = a.employee_id
WHERE e.name = 'Анна Ким'
ORDER BY project_a, project_b;

LEAST и GREATEST работают и с текстом: LEAST(имя1, имя2) берёт то, что раньше по алфавиту — так пару выводят один раз.

Всё вместе

Три отрезка Анны имеют и общее для всех троих. Начало — самый поздний из трёх стартов, конец — самый ранний из трёх концов. Если этот старт не позже этого конца, тройное пересечение есть.

SELECT
  GREATEST(a.from_date, b.from_date, c.from_date) AS overlap_start,
  LEAST(a.to_date, b.to_date, c.to_date) AS overlap_end
FROM assignments a
JOIN assignments b
  ON a.employee_id = b.employee_id
 AND a.project_id < b.project_id
JOIN assignments c
  ON a.employee_id = c.employee_id
 AND b.project_id < c.project_id
JOIN employees e ON e.id = a.employee_id
WHERE e.name = 'Анна Ким'
  AND GREATEST(a.from_date, b.from_date, c.from_date)
    <= LEAST(a.to_date, b.to_date, c.to_date);

У Дмитрия Немова тоже три назначения, но Гамма начинается в июле, когда Альфа уже кончилась. Попарно Альфа пересекается с Бетой, тройки с общим днём нет.

Коротко: точка внутри отрезка — два неравенства включительно. Два отрезка пересекаются, если каждый начался не позже конца другого. Общий кусок: GREATEST стартов и LEAST концов. Соседние дни вроде 31 августа и 1 сентября — это не пересечение.

Дальше — серии рабочих дней.