postgresql 从表中的开始和结束日期在 Postgres 中生成_series
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29023336/
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
Generate_series in Postgres from start and end date in a table
提问by SiriusBits
I have been trying to generate a series of dates (YYYY-MM-DD HH) from the first until the last date in a timestamp field. I've got the generate_series()I need, however running into an issue when trying to grab the start and end dates from a table. I have the following to give a rough idea:
我一直在尝试在时间戳字段中从第一个日期到最后一个日期生成一系列日期 (YYYY-MM-DD HH)。我有generate_series()我需要的，但是在尝试从表中获取开始和结束日期时遇到问题。我有以下几点可以给出一个粗略的想法：
with date1 as
(
SELECT start_timestamp as first_date
FROM header_table
ORDER BY start_timestamp DESC
LIMIT 1
),
date2 as
(
SELECT start_timestamp as first_date
FROM header_table
ORDER BY start_timestamp ASC
LIMIT 1
)
select generate_series(date1.first_date, date2.first_date
, '1 hour'::interval)::timestamp as date_hour
from
( select * from date1
union
select * from date2) as foo
Postgres 9.3
Postgres 9.3
回答by Erwin Brandstetter
You don't need a CTE for this, that would be more expensive than necessary.
And you don't need to cast to timestamp, the result already isof data type timestampwhen you feed timestamptypes to generate_series(). Details here:
为此您不需要 CTE，那会比必要的贵。
而且您不需要强制转换为timestamp，当您将类型提供给时，结果已经是数据类型。详情在这里：timestamptimestampgenerate_series()
In Postgres 9.3or later you can use a LATERALjoin:
在 Postgres 9.3或更高版本中，您可以使用LATERAL连接：
SELECT to_char(ts, 'YYYY-MM-DD HH24') AS formatted_ts
FROM (
SELECT min(start_timestamp) as first_date
, max(start_timestamp) as last_date
FROM header_table
) h
, generate_series(h.first_date, h.last_date, interval '1 hour') g(ts);
Optionally with to_char()to get the result as text in the format you mentioned.
（可选）以to_char()您提到的格式将结果作为文本获取。
This works in anyPostgres version:
这适用于任何Postgres 版本：
SELECT generate_series(min(start_timestamp)
, max(start_timestamp)
, interval '1 hour') AS ts
FROM header_table;
Typically a bit faster.
Calling set-returning functions in the SELECTlist is a non-standard-SQL feature and frowned upon by some. Also, there were behavioral oddities (though not for this simple case) that were eventually fixed in Postgres 10. See:
通常会快一点。
调用SELECT列表中的集合返回函数是非标准 SQL 功能，有些人不赞成。此外，在 Postgres 10 中最终修复了一些行为古怪（虽然不是这个简单的案例）。见：
Notea subtle difference in NULLhandling:
请注意NULL处理的细微差别：
The equivalent of
相当于
max(start_timestamp)
is obtained with
是用
ORDER BY start_timestamp DESC NULLS LAST
LIMIT 1Without NULLS LASTNULL values come firstin descending order (if there canbe NULL values in start_timestamp). You would get NULL for last_dateand your query would come up empty.
没有NULLS LASTNULL 值按降序排在第一位（如果 中可以有 NULL 值start_timestamp）。您将获得 NULL forlast_date并且您的查询将变为空。
Details:
细节：
回答by Gordon Linoff
How about using aggregation functions instead?
改用聚合函数怎么样？
with dates as (
SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date
FROM header_table
)
select generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour
from dates;
Or even:
甚至：
select generate_series(min(start_timestamp),
max(start_timestamp),
'1 hour'::interval
)::timestamp as date_hour
from header_table;
回答by Used_By_Already
try this:
试试这个：
with dateRange as
(
SELECT min(start_timestamp) as first_date, max(start_timestamp) as last_date
FROM header_table
)
select
generate_series(first_date, last_date, '1 hour'::interval)::timestamp as date_hour
from dateRange
NB:You want the 2 dates in arow, not on separate rows.
注意：您要在2个日期一排，而不是单独的行。
