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

Question

我有一个这样的查询，可以很好地在两个给定日期之间生成一系列日期：

 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

它在 2004-03-07 和 2004-08-16 之间生成 162 个日期，这就是我想要的。这段代码的问题在于，当两个日期来自不同年份时，它不会给出正确的答案，例如当我尝试 2007-02-01 和 2008-04-01 时。

有更好的解决方案吗？

原文由 f.ashouri 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

可以在不转换到/从 int 的情况下完成（而是转换到/从时间戳）

 SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;

原文由 wildplasser 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

要生成一系列 日期，这是 最佳 方式：

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

• 不需要额外的 date_trunc() 。转换为 date ( day::date ) 隐含地做到了这一点。

• 但是将日期文字转换为 date 作为输入参数也没有意义。相反， timestamp 是最好的选择。性能上的优势很小，但没有理由不拿下。而且您不必涉及 DST（夏令时）规则以及从 date 到 timestamp with time zone 的转换并返回。见下文。

等效的，不太明确的简短语法：

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

或者使用 SELECT 列表中的 set-returning 函数：

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

在最后一个变体中 需要 AS 关键字，否则 Postgres 会误解列别名 day 。而且我 不 建议在 Postgres 10 之前使用该变体 - 至少在同一个 SELECT 列表中没有一个以上的集合返回函数：

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

（除此之外，最后一个变体通常以微弱的优势最快。）

为什么 timestamp [without time zone] ？

有许多 generate_series() 的重载变体。目前（Postgres 11）：

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

函数签名 |返回类型
:------------------------------------------------ ------------------------------------------- | :----------------------------
generate_series(整数，整数，整数) |整数
生成系列（整数，整数）|整数
生成系列（大整数，大整数，大整数） |大整数
生成系列（大整数，大整数） |大整数
generate_series(数字,数字,数字) |数字
生成系列（数字，数字）|数字
generate_series(时间戳不带时区,时间戳不带时区,间隔) |没有时区的时间戳
generate_series(timestamp with time zone,timestamp with time zone,interval) |带时区的时间戳

（ numeric Postgres 9.5 中添加了变体。）相关的是最后两个 粗体 timestamp / timestamptz

没有变体采取或返回 date 。需要显式转换才能返回 date 。带有 timestamp 参数的调用直接解析为最佳变体，而无需降级为函数类型解析规则，也无需对输入进行额外的强制转换。

timestamp '2004-03-07' 完全有效，顺便说一句。省略的时间部分默认为 00:00 采用 ISO 格式。

由于 函数类型解析，我们仍然可以通过 date 。但这需要 Postgres 做更多的工作。从 date 到 timestamp 以及从 date 到 timestamptz 的 隐式 转换。会模棱两可，但 timestamptz 在“日期/时间类型”中是 “首选”。所以 比赛是在步骤 4d 决定的。 ：

遍历所有候选对象并将那些接受首选类型（输入数据类型的类型类别）的候选对象保留在需要类型转换的大多数位置。如果没有人接受首选类型，则保留所有候选人。如果只剩下一个候选人，请使用它；否则继续下一步。

除了函数类型解析中的额外工作之外，这还增加了对 timestamptz 的额外转换 - 这不仅会增加更多成本，还会在极少数情况下引入 DST 问题，导致意外结果。 （DST 是一个愚蠢的概念，顺便说一句，不能强调这一点。）相关：

• 如何在 PostgreSQL 中生成日期系列？
• 如何在 PostgreSQL 中生成时间序列？

我在小提琴中添加了演示，显示了更昂贵的查询计划：

_db<> 在这里 摆弄_

有关的：

• 有没有办法在 Postgres 中禁用函数重载
• 生成一系列日期 - 使用日期类型作为输入
• Postgres 数据类型转换

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议