Generate_series в Postgres от начальной и конечной даты в таблице

Question

Я пытался сгенерировать серию дат (ГГГГ-ММ-ДД ЧЧ) от первой до последней даты в поле отметки времени. У меня есть generate_series() Однако мне нужно столкнуться с проблемой при попытке получить начальную и конечную даты из таблицы. У меня есть следующее, чтобы дать грубую идею:

with date1 as
(
SELECT start_timestamp as first_date
FROM header_table
ORDER BY start_timestamp DESC
LIMIT 1
),
date2 as
(
SELECT start_timestamp as first_date
FROM header_table
ORDER BY start_timestamp ASC    
LIMIT 1
)
    select generate_series(date1.first_date, date2.first_date
                         , '1 hour'::interval)::timestamp as date_hour

from
(   select * from date1
    union
    select * from date2) as foo

Postgres 9,3

Answer 1

Вам, конечно , не нужен CTE для этого. Это будет дороже, чем необходимо.
И вам не нужно бросать на timestamp потому что результат уже имеет тип данных timestamp когда вы кормите date типы для generate_series(),

В Postgres 9.3 или новее это наиболее элегантно решается с помощью LATERAL присоединиться:

SELECT to_char(ts, 'YYYY-MM-DD HH24') AS formatted_ts
FROM  (
   SELECT min(start_timestamp) as first_date
        , max(start_timestamp) as last_date
   FROM   header_table
   ) h
  , generate_series(h.first_date, h.last_date, interval '1 hour') g(ts);

Опционально с to_char() чтобы получить результат в виде текста в формате, который вы упомянули.
В более ранних (или любых) версиях:

SELECT generate_series(min(start_timestamp)
                     , max(start_timestamp)
                     , interval '1 hour') AS ts
FROM   header_table;

Но вызывая возвращающие множество функции в SELECT Список - это нестандартная функция, которую некоторые не одобряют. Используйте первый запрос, если можете.

Обратите внимание на небольшую разницу в обработке NULL:

Эквивалент

max(start_timestamp)

получается с

ORDER BY start_timestamp DESC NULLS LAST
LIMIT 1

Без NULLS LAST Значения NULL идут первыми в порядке убывания (если в NULL могут быть значения start_timestamp). Вы получите NULL за last_date и ваш запрос будет пустым.

Подробности:

• Почему значения NULL стоят первыми при упорядочении DESC в запросе PostgreSQL?

Answer 2

Как насчет использования вместо этого функций агрегирования?

with dates as (
      SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date
      FROM header_table
     )
select generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour
from dates;

Или даже:

select generate_series(min(start_timestamp),
                       max(start_timestamp),
                       '1 hour'::interval
                      )::timestamp as date_hour
from header_table;

Answer 3

Попробуй это:

with dateRange as
  (
  SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date
  FROM header_table
  )
select 
    generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour
from dateRange

NB: Вы хотите, чтобы 2 даты в строке, а не в отдельных строках.

посмотрите это демо sqlfiddle