Определите, перекрываются ли два диапазона дат с помощью SQL

#sql #postgresql #datetime

#sql #postgresql #дата-время

Вопрос:

У нас есть ситуация в нашем собственном программном обеспечении, где мы принимаем, start_time TIMESTAMP и end_time TIMESTAMP NULL чтобы политика была эффективной. Важным моментом здесь является то, что конечная дата также может быть нулевой, что означает, что политика может действовать в течение неограниченного периода времени, если она не завершается явно. Я видел несколько решений в stackoverflow, но либо они не основаны на SQL, либо с учетом этого end_time NOT NULL . Мы хотим убедиться, что при добавлении новой политики она не перекрывается с существующей политикой.

Пример схемы для таблицы политик приведен ниже

 CREATE TABLE public.policies
(
    id uuid,
    start_time timestamp without time zone NOT NULL,
    end_time timestamp without time zone,
    PRIMARY KEY (id)
);

Пример потока данных

 INSERT INTO policies (id, start_time, end_time) VALUES
('5c056e24-9633-4bb9-b231-fbcab2d4e943', '2020-09-01 00:00:00', '2020-09-05 23:59:59'),
('4e38fd23-6f41-4022-addd-6e3feaa838e0', '2020-09-06 00:00:00', '2020-09-06 23:59:59'),
('e41fe1e5-e433-40a2-9d07-d046f8ca4bfc', '2020-09-07 00:00:00', NULL);

Любая помощь будет высоко оценена.

Ответ №1:

Вот где пригодятся диапазоны:

 select *
from policies
where tsrange(start_time, end_time, '[]') amp;amp; tsrange(check_start, check_end, '[]');

check_start и check_end будут параметрами, которые вы хотите проверить. Диапазоны в приведенном выше примере являются «закрытыми» диапазонами, т. Е. включают оба ребра. Если, например, верхняя граница является исключительной, используйте '[)' вместо этого.

Значение null равно «no end», поэтому это будет обработано автоматически.

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

1. Этот ответ выглядит многообещающим, но единственное исключение, которое я нашел, — это то, что он не передается, когда check_end имеет значение NULL

2. Я добавил пример схемы и тестовые данные для запуска. Тест, который я запустил, который не прошел, является select * from policies where (start_time, end_time) overlaps ('2020-09-01 00:00:00', null);

3. @AppDevD: если overlaps() это обрабатывается неправильно, используйте tsrange

4. Сработало красиво. Ты герой. Спасибо.

Ответ №2:

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

 select *
from table_name
where start_time < :end and (end_time is null or end_time > :start);

Ответ №3:

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

 select p.*
from policies p
where p.start_time < :end and (p.end_time > :start or p.end_time is null);

Два перекрывающихся периода времени — один начинается до окончания второго и заканчивается после начала второго.

1. Я ценю ваш быстрый ответ. Что, если для любой существующей политики end_time равно нулю, как мы это поймаем?

2. @AppDevD . . . У вас было это в вашем вопросе, не так ли. Я исправил ответ.