表中开始日期和结束日期在Postgres中的generate_series

问题描述

我一直在尝试从时间戳字段中的第一个日期到最后一个日期生成一系列日期（YYYY-MM-DD HH）。我已经有了 generate_series（），但是在尝试从表中获取开始日期和结束日期时遇到了问题。我有以下大致想法：

 ，date1为
（
选择start_timestamp作为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

解决方案

您不需要CTE，这将比必要的价格昂贵。

而且您无需转换为 timestamp ，结果当您将 timestamp 类型提供给。这里的详细信息：

在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，间隔'1 hour'）g（ts）; 
  

可选地使用可以得到您所提到的格式的文本结果。

这在 any 的Postgres版本中有效：

  SELECT generate_series（min（start_timestamp）
，max（start_timestamp）
，interval'1 hour'）ts 
 FROM header_table; 
  

通常快一点。

调用<$中的集合返回函数c $ c> SELECT 列表是一种非标准的SQL功能，有些人对此并不满意。此外，最终在Postgres 10中修复了行为异常（尽管不是这种简单情况）。

注意在SELECT子句中有多个返回集合的功能？ > NULL 处理：

相当于

  max（start_timestamp）
  

是通过

<$获得的p $ p> ORDER BY start_timestamp DESC NULLS LAST
LIMIT 1

没有 NULLS LAST NULL值以降序排在第（如果 可以是 start_timestamp ）。您将在 last_date 中得到NULL，并且查询将为空。

详细信息：

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:

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

解决方案

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 is of data type timestamp when you feed timestamp types to generate_series(). Details here:

• Generating time series between two dates in PostgreSQL

In Postgres 9.3 or later you can use a LATERAL join:

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.

This works in any Postgres version:

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 SELECT list 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:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

Note a subtle difference in NULL handling:

The equivalent of

max(start_timestamp)

is obtained with

ORDER BY start_timestamp DESC NULLS LAST
LIMIT 1

Without NULLS LAST NULL values come first in descending order (if there can be NULL values in start_timestamp). You would get NULL for last_date and your query would come up empty.

Details:

• Why do NULL values come first when ordering DESC in a PostgreSQL query?

这篇关于表中开始日期和结束日期在Postgres中的generate_series的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！