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

小开

最佳答案

不需要转换为/from int (而是转换为/from time戳)

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

小开

您可以直接使用日期生成序列。不需要使用 int 或时间戳:

select date::date
from generate_series(
'2004-03-07'::date,
'2004-08-16'::date,
'1 day'::interval
) date;

小开

要生成一系列的日期，这是最佳的方法:

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-return 函数:

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';

function_signature                                                                | return_type
:-------------------------------------------------------------------------------- | :--------------------------
generate_series(integer,integer,integer)                                          | integer
generate_series(integer,integer)                                                  | integer
generate_series(bigint,bigint,bigint)                                             | bigint
generate_series(bigint,bigint)                                                    | bigint
generate_series(numeric,numeric,numeric)                                          | numeric
generate_series(numeric,numeric)                                                  | numeric
generate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zone
generate_series(timestamp with time zone,timestamp with time zone,interval)       | timestamp with time zone

(在 Postgres 9.5中添加了 numeric变体。)相关的是最后两次 粗体字摄取和返回 timestamp/timestamptz。

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

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

感谢 < strong > 函数类型分辨率，我们仍然可以通过 date。但这需要 Postgres 做更多的工作。有一个从 date到 timestamp的 [ em ]隐式[ em ]演员，以及一个从 date到 timestamptz的 [ em ]隐式[ em ]演员。将是模糊的，但 timestamptz是“日期/时间类型”中的 “首选”。所以比赛在步骤 < em > 4d 决定:

检查所有候选人，并保留那些接受首选类型的候选人 (指输入数据类型的类型类别)在大多数位置需要进行类型转换。如果没有接受，请保留所有候选人首选类型。如果只剩下一个候选类型，则使用它; 否则继续下一步。

除了在函数类型分辨率方面的额外工作，这给 timestamptz增加了额外的强制转换——这不仅增加了更多的成本，还可能引入 DST 问题，在极少数情况下导致意想不到的结果。(DST 是一个愚蠢的概念，顺便说一句，怎么强调都不为过。)相关阅读:

我在小提琴上添加了演示，展示了更昂贵的查询计划:

分贝 < > 小提琴给你

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

为什么是 timestamp [without time zone]？

为什么是 `timestamp [without time zone]`？