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

小编典典

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

sql

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

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和之间生成162个日期2004-08-16，这正是我想要的。此代码的问题在于，当两个日期来自不同年份时（例如，当我尝试使用2007-02-01和时），它将无法给出正确的答案2008-04-01。

有更好的解决方案吗？

阅读 474

2021-04-15

共1个答案

小编典典

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

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列表中的返回设置函数：

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

该AS关键字需要在最后的变体，Postgres的会曲解列别名day除外。而且我不建议在Postgres 10之前使用该变体-至少不要在同一SELECT列表中具有一个以上的set-returning函数：

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
(

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

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

由于函数类型解析，我们仍然可以通过date。但这需要Postgres进行更多工作。有一个隐含的投从date到timestamp以及从一个date到timestamptz。会模棱两可，但在“日期/时间类型”中timestamptz是“首选”。因此，比赛是在第4d步决定的。：

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

除了在函数类型解析方面的额外工作之外，这还增加了额外的转换timestamptz-这不仅增加了成本，而且还可能引入DST问题，在极少数情况下会导致意外结果。（顺便说一句，DST是一个愚蠢的概念，对此压力还不够。）

2021-04-15