在 Postgres 中从表中的开始日期和结束日期开始Generate_series

Question

我一直在尝试在时间戳字段中生成从第一个日期到最后一个日期的一系列日期（YYYY-MM-DD HH）。我有我需要的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

邮政 9.3

Answer 1

您不需要CTE，那将比必要的贵。
而且你不需要投射到timestamp，当你将timestamp类型提供给generate_series()时，结果已经是数据类型timestamp。详情请见此处：

• 在PostgreSQL中生成两个日期之间的时间序列

在 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() 以您提到的格式将结果作为文本获取。

这适用于任何 Postgres 版本：

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

通常速度更快一些。
在SELECT列表中调用集合返回函数是一项非标准的 SQL 功能，有些人不赞成。此外，还有一些行为上的怪异（尽管不是针对这个简单的案例）最终在Postgres 10中得到了修复。看：

• SELECT 子句中多个集合返回函数的预期行为是什么？

请注意NULL 处理的细微差异：

相当于

max(start_timestamp)

是用

ORDER BY start_timestamp DESC NULLS LAST
LIMIT 1

如果没有NULLS LAST NULL 值按降序排在第一位（如果 start_timestamp 中可以有 NULL 值）。您将获得 NULL last_date并且您的查询将为空。

详：

• 为什么在 PostgreSQL 查询中对 DESC 进行排序时，NULL 值排在第一位？

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

注意：您希望连续 2 个日期，而不是在单独的行上。

查看此 SQLFIDDLE 演示