在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时。

有更好的解决方案吗?

Answer 1

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

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

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

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

AS关键字是必需的在最后一个变体中，否则Postgres会误解列别名day。我将不建议在Postgres 10之前的变体-至少不要在同一个SELECT列表中使用多个set返回函数:

• 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) | integerGenerate_series (integer,integer) | integerGenerate_series (bigint,bigint,bigint) | bigintGenerate_series (bigint,bigint) | bigintGenerate_series (numeric,numeric,numeric) | numericGenerate_series (numeric,numeric) | numericgenerate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zoneGenerate_series (timestamp with time zone,timestamp with time zone,interval) | timestamp with time zone之前

(numeric变体在Postgres 9.5中被添加)相关的是最后两个，黑体取并返回timestamp/timestamptz。

没有接受或返回date 的变体。需要显式强制转换来返回date。带timestamp参数的调用直接解析为最佳变量，而不需要下降到函数类型解析规则中，也不需要对输入进行额外的强制转换。

顺便说一句，

timestamp '2004-03-07'是完全有效的。省略的时间部分默认为ISO格式的00:00。

由于函数类型解析，我们仍然可以传递date。但是这需要Postgres做更多的工作。有一个隐式转换从date到timestamp，以及一个从date到timestamptz。将是模棱两可的，但timestamptz是"首选"在"日期/时间类型"。因此，在步骤4d中确定匹配。:

遍历所有候选项并保留那些接受首选类型的候选项(输入数据类型的类型类别)在其中的大多数位置需要进行类型转换。如果没有人接受，请保留所有候选人喜欢的类型。如果只剩下一个候选人，就使用它;其他的继续到下一步。

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

如何在PostgreSQL中生成一个日期序列?如何在PostgreSQL中生成时间序列?

我添加了演示，展示了更昂贵的查询计划:

db<>小提琴这里

相关:

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

Answer 3

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

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

Answer 4

你也可以使用这个

select generate_series  ( '2012-12-31'::timestamp , '2018-10-31'::timestamp , '1 day'::interval) :: date