generate_steries在夏令时上更改-不同的结果取决于服务器时区

Question

当我的postgres服务器位于美国/纽约时区，或者我使用SET SESSION TIME ZONE 'America/New_York'时，generate_series会考虑夏令时的变化，我可以获得我想要的正确历元或时间点：

postgres=# SET SESSION TIME ZONE 'America/New_York';
SET
postgres=#
postgres=# with seq(ts) as (select * from generate_series('2018-11-03 00:00:00.000 -04:00', '2018-11-06 13:40:39.067 -05:00', '1d'::interval))
postgres-# select ts, extract(epoch from ts) as epoch, ts at time zone 'America/New_York' as eastern
postgres-# from seq;
ts           |   epoch    |       eastern
------------------------+------------+---------------------
2018-11-03 00:00:00-04 | 1541217600 | 2018-11-03 00:00:00
2018-11-04 00:00:00-04 | 1541304000 | 2018-11-04 00:00:00
2018-11-05 00:00:00-05 | 1541394000 | 2018-11-05 00:00:00
2018-11-06 00:00:00-05 | 1541480400 | 2018-11-06 00:00:00
(4 rows)

但是，当我的服务器处于UTC生产状态时，generate_series不考虑夏令时的变化：

postgres=# SET SESSION TIME ZONE 'UTC';
SET
postgres=# with seq(ts) as (select * from generate_series('2018-11-03 00:00:00.000 -04:00', '2018-11-06 13:40:39.067 -05:00', '1d'::interval))
postgres-# select ts, extract(epoch from ts) as epoch, ts at time zone 'America/New_York' as eastern
postgres-# from seq;
ts           |   epoch    |       eastern
------------------------+------------+---------------------
2018-11-03 04:00:00+00 | 1541217600 | 2018-11-03 00:00:00
2018-11-04 04:00:00+00 | 1541304000 | 2018-11-04 00:00:00
2018-11-05 04:00:00+00 | 1541390400 | 2018-11-04 23:00:00
2018-11-06 04:00:00+00 | 1541476800 | 2018-11-05 23:00:00
(4 rows)

通知11/4和11/5未针对夏令时变更进行调整。

在我查询时，如果不设置会话时区，有什么方法可以解决这个问题吗？

使用postgres 9.6…

Answer 1

会话时区控制着如何解释1天的间隔，所以我认为你的问题的答案很简单，没有。

Postgres文档对此的解释如下：

将间隔值添加到具有时区值的时间戳(或从中减去间隔值(时，天数组件会将具有时区的时间戳的日期提前或减少所指示的天数。在夏令时变化期间(当会话时区设置为识别夏令时的时区时(，这意味着interval '1 day'不一定等于interval '24 hours'。例如，当会话时区设置为CST7CDT时，timestamp with time zone '2005-04-02 12:00-07' + interval '1 day'将产生timestamp with time zone '2005-04-03 12:00-06'，而将interval '24 hours'添加到与时区相同的初始时间戳会产生timestamp with time zone '2005-04-03 13:00-06'，因为时区CST7CDT中的2005-04-03 02:00的夏令时发生了变化。

据我所知，除了将区间设置为会话时区之外，没有其他机制可以告诉您如何在特定时区中解释区间。换句话说，我认为您正在寻找类似'1 day tz America/New_York'::interval的东西，但我不相信存在这样的语法。

Answer 2

您可以在连接时为特定用户(ALTER USER(或特定数据库(ALTER database(自动设置时区。

我唯一能想到的另一种方法是使用函数。

BEGIN;
CREATE FUNCTION f1() RETURNS SETOF timestamptz
AS $$
SELECT generate_series('2018-11-03 00:00:00-04', '2018-11-06 00:00:00-05', interval '1 day');
$$
LANGUAGE sql
SET timezone = 'America/New_York';                                                                                                               
SET timezone = 'UTC';
SELECT generate_series('2018-11-03 00:00:00-04', '2018-11-06 00:00:00-05', interval '1 day');
SELECT * FROM f1();
ROLLBACK;

产品：

generate_series
------------------------
2018-11-03 04:00:00+00
2018-11-04 04:00:00+00
2018-11-05 04:00:00+00
2018-11-06 04:00:00+00
(4 rows)
f1
------------------------
2018-11-03 04:00:00+00
2018-11-04 04:00:00+00
2018-11-05 05:00:00+00
2018-11-06 05:00:00+00
(4 rows)

Answer 3

您可以尝试在所需时区生成序列，然后转换为UTC：

select generate_series('2018-11-03 00:00:00.000', '2018-11-06 13:40:39.067', '1d'::interval)::timestamp at time zone 'America/New_York'

输出：

2018-11-03 04:00:00+00
2018-11-04 04:00:00+00
2018-11-05 05:00:00+00
2018-11-06 05:00:00+00