<rmcreative>

RSS

SQL, пересекается ли событие с теми, что уже имеются в таблице

9 августа 2011

Довольно частая задача при работе с различными событиями и датами.

Имеется таблица вида:

CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
);

Тут start_time и end_time не обязательно datetime. Например, они могут быть просто int-ами, хранящими обычный unix timestamp.

Перед вставкой очередной записи нужно убедиться, что событие не пересечётся с каким-либо уже имеющимся в базе. Решается это так:

SELECT 1
FROM event
WHERE 
  :new_start_time BETWEEN start_time AND end_time 
  OR :new_end_time BETWEEN start_time AND end_time 
  OR start_time BETWEEN :new_start_time AND :new_end_time 
  OR end_time BETWEEN :new_start_time AND :new_end_time
LIMIT 1

В запросе :new_start_time и :new_end_time соответственно начало и конец события, которое мы собираемся вставлять. Если запрос выше отдал 1, наше новое событие с чем-то пересекается…

UPD: менее избыточные варианты (спасибо dec5e):

NOT(:new_start_time > end_time OR :new_end_time < start_time)

или через AND

:new_start_time <= end_time AND :new_end_time >= start_time

Комментарии RSS

  1. №5158
    Romanoza
    Romanoza 10.08.2011, 11:03:11
    OR :new_end_time BETWEEN start_time AND end_time 
     
     
     
    OR start_time BETWEEN :new_start_time AND :new_end_time

    а это не одно и то же?

  2. №5159
    Ilya
    Ilya 10.08.2011, 11:51:23

    Нет, это не одно и то же. Представьте (или нарисуйте), что новый интервал лежит полностью в старом (new_start_time > start_time && new_end_time < end_time). Первое условие тогда сработает, а второе - нет.

  3. №5160
    dec5e
    dec5e 10.08.2011, 14:46:13

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

    NOT(:new_start_time > end_time OR :new_end_time < start_time)

    или через AND

    :new_start_time <= end_time AND :new_end_time >= start_time
  4. №5162
    Sam
    Sam 10.08.2011, 21:38:34

    dec5e, упустил. Тест-кейс: в базе есть отрезок 4…6, добавляем 1…10.

  5. №5163
    dec5e
    dec5e 11.08.2011, 12:05:18

    Ну отработает же тест-кейс! :)

    1<=6 AND 10>=4

    Получаем TRUE, значит пересекаются. И вариант с выбрасыванием одной любой проверки из 4 оригинальных тоже срабатывает.

  6. №5168
    Максим
    Максим 11.08.2011, 18:05:02

    Все верно, согласен с dec5e

  7. №5170
    Sam
    Sam 11.08.2011, 23:55:51

    dec5e, действительно отработает. Спасибо.

  8. №5201
    Big_Shark
    Big_Shark 17.08.2011, 12:27:59

    Была несколько иная задача, нужно было вытащить все события входящие в промежуток между дат, решил так

    (`begin` <= '$begin' AND `end` >= '$begin') OR 
    (`begin` <= '$end' AND `end` >= '$end')
  1. Почта опубликована не будет.

  2. Можно использовать синтаксис Markdown или HTML.

  3. Введите ответ в поле. Щёлкните, чтобы получить другую задачу.