带有时区的Postgresql date_trunc将区域移动1小时

Question

我们使用的是Postgresql9.4，在使用date_trunc时我注意到了一个奇怪的行为。结果中的时区移动了1小时：

select date_trunc('year','2016-08-05 04:01:58.372486-05'::timestamp with time zone);
       date_trunc
------------------------
2016-01-01 00:00:00-06

当截断到例如day时没有这样的行为：

select date_trunc('day','2016-08-05 04:01:58.372486-05'::timestamp with time zone);
       date_trunc
------------------------
2016-08-05 00:00:00-05

这是预期的行为吗？如果是这样，背后的逻辑是什么？

Answer 1

date_trunc(text, timestamptz)变体似乎有点文档不足，所以以下是我的发现：

1( 在day精度(第一个参数(以下，结果的时区偏移量始终与第二个参数的偏移量相同。

2( 在day精度或以上时，根据当前TimeZone配置参数(可使用set time zone '...'或set TimeZone to '...'设置(重新计算时区偏移。重新计算的偏移量始终与具有相同TimeZone配置参数的精确时刻相同。因此，例如，当TimeZone参数包含DST信息时，偏移量会相应对齐。然而，当实际的TimeZone参数不包含DST信息(例如固定偏移(时，结果的时区偏移不受影响。

总之，date_trunc(text, timestamptz)函数可以用date_trunc(text, timestamp)变体和at time zone算子来模拟s:

date_trunc('month', tstz)

应等同于：

date_trunc('month', tstz at time zone current_setting('TimeZone')) at time zone current_setting('TimeZone'))

至少，我是这么想的。事实证明，存在一些TimeZone配置设置，这些设置存在问题。因为：

PostgreSQL允许您以三种不同的形式指定时区：

• 完整时区名称，例如America/New_York。pg_timezone_names视图中列出了已识别的时区名称(见第50.80节(。PostgreSQL为此使用了广泛使用的IANA时区数据，因此许多其他软件也可以识别相同的时区名称。

• 时区缩写，例如PST。这样的规范仅定义了UTC的特定偏移量，而完整时区名称也可能意味着一组夏令时转换日期规则。已识别的缩写在pg_timezone_abbrevs视图中列出(请参阅第50.79节(。您不能将配置参数TimeZone或log_timezone设置为时区缩写，但您可以在日期/时间输入值中使用缩写，并使用AT time zone运算符。

(第三个是修复偏移量，或者它的POSIX形式，但这在这里并不重要(。

如您所见，缩写不能设置为TimeZone。但也有一些缩写，也被认为是一个完整时区名称，例如CET。因此，set time zone 'CET'将成功，但实际上将在夏季使用CEST。但at time zone 'CET'将始终引用缩写，这是相对于UTC的固定偏移(而不是CEST，因为它可以使用at time zone 'CEST'；但set time zone 'CEST'无效(。

以下是时区设置的完整列表，当它们在set time zone中使用时与在at time zone中使用时(自9.6起(具有不兼容的含义：

CET
EET
MET
WET

使用以下脚本，您可以检查您的版本：

create or replace function incompatible_tz_settings()
  returns setof text
  language plpgsql
as $func$
declare
  cur cursor for select name from pg_timezone_names;
begin
  for rec IN cur loop
    declare
      r pg_timezone_names;
    begin
      r := rec;
      execute format('set time zone %L', (r).name);
      if exists(select 1
                from   generate_series(current_timestamp - interval '12 months', current_timestamp + interval '12 months', interval '1 month') tstz
                where  date_trunc('month', tstz) <> date_trunc('month', tstz at time zone (r).name) at time zone (r).name) then
        return next (r).name;
      end if;
    end;
  end loop;
end
$func$;

http://rextester.com/GBL17756

Answer 2

预计date_trunc有两种变体：一种用于timestamp，另一种用于timestamptz，因为文档中说：

下面描述的所有需要时间或时间戳输入实际上有两种变体：一种需要时间带时区或带时区的时间戳，以及一个需要时间的时间戳没有时区或没有时区的时间戳。为了简洁起见变体没有单独显示。

如果你想更好地理解时间戳和时间戳，请先阅读这里的好答案。

然后是关于CCD_ 35。根据我的实验和对各种SO答案的解释(比如这个(，当接收到时间戳ptz时，date_trunc首先将其转换为时间戳。此转换返回本地时间的时间戳。然后执行截断：只保留日期并删除小时/分钟/秒。

为了避免这种转换(感谢pozs(，请为date_trunc:提供时间戳(而不是时间戳ptz(

date_trunc('day', TIMESTAMPTZ '2001-07-16 23:38:40Z' at time zone 'UTC')

CCD_ 37部分表示"将此时间戳ptz转换为UTC时间的时间戳"(小时不受此转换的影响(。然后date_trunc返回2001-07-16 00:00:00。