我有这样一个查询,很好地生成一系列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
它产生162个之间日期2004-03-07
和2004-08-16
,这就是我想要的。 这段代码的问题是,它不会得到正确的答案时,这两个日期都不同年份,例如,当我试图2007-02-01
和2008-04-01
。
有没有更好的解决办法?
无需转换即可(从时间戳,但到/替代)来完成到/从int
SELECT date_trunc('day', dd):: date
FROM generate_series
( '2007-02-01'::timestamp
, '2008-04-01'::timestamp
, '1 day'::interval) dd
;
这应该是最佳的方式:
SELECT day::date
FROM generate_series(timestamp '2004-03-07'
, timestamp '2004-08-16'
, interval '1 day') AS t(day);
等效短语法:
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
列表:
- 什么是在SELECT子句中多组返回功能预期的行为?
为什么?
有许多的重载变体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(整数,整数,整数)| 整数 generate_series(整数,整数)| 整数 generate_series(BIGINT,BIGINT BIGINT)| BIGINT generate_series(BIGINT,BIGINT)| BIGINT generate_series(数字,数字,数字)| 数字 generate_series(数字,数字)| 数字 generate_series(没有时间戳时区,没有时间戳时区,间隔)| 时间戳无时区 generate_series(时间戳与时区,时间戳和时间区,间隔)| 时间戳和时区
(该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
。 演员到timestamptz
不仅增加了更多的成本,还可以引入问题DST导致罕见的情况下意外的结果。 (DST是鲁钝概念,顺便说一句,不能强调这一点。)相关:
- 如何生成PostgreSQL中枣系列?
- 如何生成PostgreSQL中的时间序列?
我加了演示,展示了更昂贵的查询计划的小提琴:
dbfiddle 这里
有关:
- 有没有一种方法来禁用在Postgres的函数重载
- 产生一系列的时间-日期使用类型作为输入
- Postgres的数据类型转换
您可以直接与日期产生系列。 无需使用整数或时间戳:
select date::date
from generate_series(
'2004-03-07'::date,
'2004-08-16'::date,
'1 day'::interval
) date;
你可以像使用
选择generate_series( '2012-12-31' ::时间戳, '2018年10月31日' ::时间戳, '1天' ::间隔)::日期