Генерация временных рядов между двумя датами в PostgreSQL

У меня есть такой запрос, который приятно генерирует серию дат между двумя данными датами:

select date '2004-03-07' + j - i as AllDate 
from generate_series(0, extract(doy from date '2004-03-07')::int - 1) as i,
     generate_series(0, extract(doy from date '2004-08-16')::int - 1) as j

Генерирует 162 даты между 2004-03-07 а также 2004-08-16 и это то, что я хочу. Проблема с этим кодом состоит в том, что он не даст правильного ответа, когда две даты относятся к разным годам, например, когда я пытаюсь 2007-02-01 а также 2008-04-01,

Есть ли лучшее решение?

Источник

4 ответа

Решение

Может быть сделано без преобразования в / из int (но вместо этого в / из отметки времени)

SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;
Источник

Есть два ответа (пока). Оба работают, но оба неоптимальны. Вот третий:

SELECT day::date 
FROM   generate_series(timestamp '2004-03-07'
                     , timestamp '2004-08-16'
                     , interval  '1 day') day;

  • Нет необходимости в дополнительном date_trunc(), Актерский состав date (day::date) делает это неявно.

  • Но также нет смысла в литералах даты приведения к date в качестве входного параметра. Au наоборот timestampэто лучший выбор здесь. Преимущество в производительности невелико, но нет причин не принимать его. И вам не нужно задействовать правила DST в сочетании с типом данных timestamp with time zone, Смотрите объяснение ниже.

Более короткий эквивалент:

SELECT day::date 
FROM   generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') day;

Или даже с функцией возврата набора в SELECT список:

SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS day;

AS здесь необходимо ключевое слово, поскольку псевдоним столбца day иначе будет неправильно понято.

Я бы не советовал использовать последний до Postgres 10, по крайней мере, не с более чем одной возвращающей множество функцией в одной и той же SELECT список. Увидеть:

Зачем?

Есть несколько перегруженных вариантов generate_series(), В настоящее время (Postgres 10):

SELECT oid::regprocedure   AS function_signature
     , prorettype::regtype AS return_type
FROM   pg_proc
where  proname = 'generate_series';

функция_подпись | return_type:-------------------------------------------------------------------------------- |:--------------------------
generate_series(целое, целое, целое)                                          | целое число generate_series (целое число, целое число)                                          | целое число generate_series(bigint,bigint,bigint)                                             | bigint                     
generate_series(bigint,bigint)                                                    | bigint                     
generate_series(числовой, числовой, числовой)                                          | числовой генерировать_серии (числовые, числовые)                                          | numericgenerate_series (отметка времени без часового пояса, отметка времени без часового пояса, интервал)                                          |временная метка без часового пояса generate_series (временная метка с часовым поясом, временная метка с часовым поясом, интервал)                                          |временная метка с часовым поясом

Вариант взятия и возврата numeric был добавлен Postgres 9.5. Но единственные уместные здесь - последние два в смелом взятии и возвращении timestamp / timestamptz,

Как вы можете видеть, нет варианта принятия или возвратаdate, Вот почему нам нужно явное приведение, если мы хотим вернуть date, Переходя timestamp разрешает правильную функцию напрямую, без необходимости опускаться в правила разрешения типов функций и без дополнительного приведения для ввода.

А также timestamp '2004-03-07' совершенно верно. Часть времени по умолчанию 00:00 если опущено

Благодаря разрешению типа функции мы все еще можем передать date, Но это требует больше работы от Postgres. Существует неявное приведение date в timestamp а также из date в timestamptz, Было бы неоднозначно, но timestamptz является "предпочтительным" среди "типов даты / времени". Итак, матч решен на шаге 4d.:

Пройдите через всех кандидатов и оставьте те, которые принимают предпочтительные типы (из категории типов входных типов данных), на большинстве позиций, где потребуется преобразование типов. Сохраните всех кандидатов, если ни один не принимает предпочтительные типы. Если остается только один кандидат, используйте его; иначе перейдите к следующему шагу.

В дополнение к дополнительной работе в разрешении типа функции это добавляет дополнительное приведение к timestamptz, Актерский состав timestamptz это не только увеличивает стоимость, но также может создавать проблемы с переходом на летнее время, что в редких случаях приводит к неожиданным результатам. (DST - это дебильная концепция, кстати, не могу не подчеркнуть это достаточно.)

Я добавил демо в скрипку, чтобы показать более дорогой план запроса:

dbfiddle здесь

Связанные с:

Источник

Вы можете создавать серии непосредственно с датами. Нет необходимости использовать метки или временные метки:

select date::date 
from generate_series(
  '2004-03-07'::date,
  '2004-08-16'::date,
  '1 day'::interval
) date;
Источник

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

выберите generate_series ( '2012-12-31':: отметка времени, '2018-10-31':: отметка времени, '1 день':: интервал):: дата

Источник
Другие вопросы по тегам