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

小编典典

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

sql

我一直在尝试从时间戳字段中的第一个日期到最后一个日期生成一系列日期（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

Postgres 9.3

阅读 243

2021-05-05

共1个答案

小编典典

您不需要CTE，这将比必要的成本更高。
而且您不需要强制转换为timestamp，当您向类型提供数据时，结果已经是
数据类型。详细信息在这里：timestamp``timestampgenerate_series()

在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中修复了行为异常（尽管不是针对这种简单情况）。

注意在 NULL 处理方面的细微差别：

相当于

max(start_timestamp)

是用

ORDER BY start_timestamp DESC **NULLS LAST**
LIMIT 1

如果没有NULLS LASTNULL值来首次在从大到小的顺序（如果有 _可能_是空值start_timestamp）。您将获得NULL，last_date而查询将变为空。

2021-05-05