在 PostgreSQL 中生成两个日期之间的时间序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14113469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generating time series between two dates in PostgreSQL
提问by f.ashouri
I have a query like this that nicely generates a series of dates between 2 given dates:
我有一个这样的查询，它很好地生成了 2 个给定日期之间的一系列日期：
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
It generates 162 dates between 2004-03-07and 2004-08-16and this what I want. The problem with this code is that it wouldn't give the right answer when the two dates are from different years, for example when I try 2007-02-01and 2008-04-01.
它生成 162 个日期2004-03-07和2004-08-16我想要的。这段代码的问题在于，当两个日期来自不同的年份时，它不会给出正确的答案，例如当我尝试2007-02-01和 时2008-04-01。
Is there a better solution?
有更好的解决方案吗？
回答by wildplasser
Can be done without conversion to/from int (but to/from timestamp instead)
可以在不转换为/从 int 的情况下完成（而是转换为/从时间戳）
SELECT date_trunc('day', dd):: date
FROM generate_series
( '2007-02-01'::timestamp
, '2008-04-01'::timestamp
, '1 day'::interval) dd
;
回答by Erwin Brandstetter
To generate a series of datesthis is the optimalway:
要生成一系列日期，这是最佳方式：
SELECT t.day::date
FROM generate_series(timestamp '2004-03-07'
, timestamp '2004-08-16'
, interval '1 day') AS t(day);
Additional
date_trunc()is not needed. The cast todate(day::date) does that implicitly.But there is also no point in casting date literals to
dateas input parameter. Au contraire,timestampis the best choice. The advantage in performance is small, but there is no reason not to take it. And you do not needlessly involve DST (daylight saving time) rules coupled with the conversion fromdatetotimestamp with time zoneand back. See below.
date_trunc()不需要额外的。强制转换为date(day::date) 隐含地做到了这一点。但是将日期文字转换
date为输入参数也没有意义。相反，timestamp是最好的选择。性能上的优势很小，但没有理由不去拿。而你不无谓地涉及DST（夏令时间）的规则加上从转换date到timestamp with time zone和背部。见下文。
Equivalent, less explicit short syntax:
等效的、不太明确的简短语法：
SELECT day::date
FROM generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') day;
Or with the set-returning function in the SELECTlist:
或者使用SELECT列表中的设置返回函数：
SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS day;
The ASkeyword is requiredin the last variant, Postgres would misinterpret the column alias dayotherwise. And I would notadvise that variant before Postgres 10 - at least not with more than one set-returning function in the same SELECTlist:
该AS关键字需要在最后的变体，Postgres的会曲解列别名day除外。而且我不建议在 Postgres 10 之前使用该变体 - 至少在同一个SELECT列表中不会有多个集合返回函数：
(That aside, the last variant is typically fastest by a tiny margin.)
（除此之外，最后一个变体通常以很小的幅度最快。）
Why timestamp [without time zone]?
为什么timestamp [without time zone]？
There are a number of overloaded variants of generate_series(). Currently (Postgres 11):
有许多重载的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
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
(numericvariants were added with Postgres 9.5.) The relevant ones are the last two in boldtaking and returning timestamp/ timestamptz.
（numeric在 Postgres 9.5 中添加了变体。）相关的是最后两个以粗体显示和返回timestamp/ timestamptz。
There is no variant taking or returning date. An explicit cast is needed to return date. The call with timestamparguments resolves to the best variant directly without descending into function type resolution rules and without additional cast for the input.
有没有变异服用或返回date。需要显式转换才能返回date。带timestamp参数的调用直接解析为最佳变体，无需下降到函数类型解析规则，也无需对输入进行额外的强制转换。
timestamp '2004-03-07'is perfectly valid, btw. The omitted time part defaults to 00:00with ISO format.
timestamp '2004-03-07'是完全有效的，顺便说一句。省略的时间部分默认00:00使用 ISO 格式。
Thanks to function type resolutionwe can still pass date. But that requires more work from Postgres. There is an implicitcastfrom dateto timestampas well as one from dateto timestamptz. Would be ambiguous, but timestamptzis "preferred"among "date/time types". So the match is decided at step 4d.:
由于函数类型解析，我们仍然可以通过date. 但这需要 Postgres 做更多的工作。有一个隐含的投从date到timestamp以及从一个date到timestamptz。会模棱两可，但在“日期/时间类型”中timestamptz是“首选”。所以匹配在步骤4d决定。：
Run through all candidates and keep those that accept preferred types (of the input data type's type category) at the most positions where type conversion will be required. Keep all candidates if none accept preferred types. If only one candidate remains, use it; else continue to the next step.
遍历所有候选项，并将那些接受首选类型（输入数据类型的类型类别）的候选项保留在需要类型转换的最多位置。如果没有人接受首选类型，则保留所有候选人。如果只剩下一名候选人，则使用它；否则继续下一步。
In addition to the extra work in function type resolution this adds an extra cast to timestamptz- which not only adds more cost, it can also introduce problems with DST leading to unexpected results in rare cases. (DST is a moronic concept, btw, can't stress this enough.) Related:
除了函数类型解析方面的额外工作之外，这还增加了额外的强制转换timestamptz- 这不仅会增加更多成本，还会引入 DST 问题，导致在极少数情况下出现意外结果。（DST 是一个愚蠢的概念，顺便说一句，不能强调这一点。）相关：
I added demos to the fiddle showing the more expensive query plan:
我在小提琴中添加了演示，显示了更昂贵的查询计划：
db<>fiddle here
db<>在这里摆弄
Related:
有关的：
回答by fbonetti
You can generate series directly with dates. No need to use ints or timestamps:
您可以直接使用日期生成系列。无需使用整数或时间戳：
select date::date
from generate_series(
'2004-03-07'::date,
'2004-08-16'::date,
'1 day'::interval
) date;
回答by Meyyappan
You can also use this.
你也可以使用这个。
select generate_series ( '2012-12-31'::timestamp , '2018-10-31'::timestamp , '1 day'::interval) :: date
