postgresql `at time zone` 不正确的行为？

Question

我有一个包含以下数据的简单 postgresql 表:

create table ti (
  l text,
  t timestamp without time zone
  );

insert into ti(l, t) values ('now', now());

我使用 at time zone 进行选择，我预计 UTC + 5.30 小时

select now(), t, t at time zone 'Asia/Kolkata' from ti;

但这就是我得到的:

now|    t|  timezone
2019-06-06T12:11:42.576388Z|    2019-06-06T11:50:48.178689Z|    2019-06-06T06:20:48.178689Z

它减去而不是增加 5.30 小时。

SQLfiddle here

Answer 1

now() 返回带时区的时间戳。当将时区信息保存在您的表中时，当它被转换为没有时区的时间戳时，时区信息将被剥离，但保存在其中的实际值将取决于 session 时区。

您可以很容易地看到这种行为:

postgres=# begin;
BEGIN
postgres=# set time zone utc;
SET
postgres=# insert into test select now();
INSERT 0 1
postgres=# set time zone 'US/Eastern';
SET
postgres=# insert into test select now();
INSERT 0 1
postgres=# select * from test;
             a
----------------------------
 2019-06-06 12:46:10.475424
 2019-06-06 08:46:10.475424
(2 rows)

在您发表评论后再做一些解释。我认为您遇到的问题是在 timestamp 和 timestamptz 之间进行转换。如果您坚持只使用 timestamptz，时间戳就不会那么困惑。让我们从讨论中删除 now()，因为这会增加一层额外的复杂性，因为从 now() 的结果转换为不带时区的时间戳取决于 session 时区。

select '2019-06-06 12:00:00UTC'::timestamp with time zone, 
       ('2019-06-06 12:00:00UTC'::timestamp with time zone) at time zone 'Asia/Kolkata';
      timestamptz       |      timezone
------------------------+---------------------
 2019-06-06 12:00:00+00 | 2019-06-06 17:30:00

我相信这就是您所期望的？我们在特定时区将带时区的时间戳转换为不带时区的时间戳。

不过，您所做的与此类似:

select '2019-06-06 12:00:00UTC'::timestamp with time zone, 
  (('2019-06-06 12:00:00UTC'::timestamp with time zone)::timestamp without time zone) at time zone 'Asia/Kolkata';
      timestamptz       |        timezone
------------------------+------------------------
 2019-06-06 12:00:00+00 | 2019-06-06 06:30:00+00
(1 row)

如果您可以存储带时区的时间戳而不是不带时区的时间戳，我想您会发现这不会那么困惑。