Postgres поиск доступных временных интервалов с generate_series

Question

У меня есть таблица в моей базе данных postgres, в которой есть столбец дат. Я хочу найти, какая из этих дат отсутствует - например:

date
2016-11-09 18:30:00
2016-11-09 19:00:00
2016-11-09 20:15:00
2016-11-09 22:20:00
2016-11-09 23:00:00

Вот, |2016-11-09 21:00:00| пропал, отсутствует. После сортировки сгенерированной серии, если в моей таблице есть запись между двумя слотами (интервал с интервалом 1 час), мне нужно удалить это.

Я хочу сделать запрос с generate_series это возвращает мне дату, которая отсутствует. Это возможно?.

Пример запроса, который я использовал для генерации серии.

    SELECT t
    FROM generate_series(
       TIMESTAMP WITH TIME ZONE '2016-11-09 18:00:00',
       TIMESTAMP WITH TIME ZONE '2016-11-09 23:00:00',
       INTERVAL '1 hour'
    ) t
    EXCEPT
    SELECT tscol
    FROM mytable;

Но этот запрос не удаляет 2016-11-09 18:30:00,2016-11-09 20:15:00 и т. Д. Потому что я использовал, кроме.

Answer 1

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

EXCEPTздесь не работает, потому что он выполняет сравнение на равенство, а вы хотите проверить, существует ли запись в пределах диапазона.

Типичным решением для этого является использование антипаттерна левого соединения:

select dt
from generate_series(
    timestamp with time zone '2016-11-09 18:00:00',
    timestamp with time zone '2016-11-09 23:00:00',
    interval '1 hour'
) d(dt)
left join mytable t
    on t.tscol >= dt and t.tscol < dt + interval '1 hour'
where t.tscol is null

Вы также можете использовать not exists:

select dt
from generate_series(
    timestamp with time zone '2016-11-09 18:00:00',
    timestamp with time zone '2016-11-09 23:00:00',
    interval '1 hour'
) d(dt)
where not exists (
    select 1
    from mytable t
    where t.tscol >= dt and t.tscol < dt + interval '1 hour'
)

В этой демонстрации DB Fiddle оба запроса возвращают:

| dt |
|: --------------------- |
| 2016-11-09 21: 00: 00 +00 |