Postgresql 选择缺少时区偏移量的输出日期

Question

我在 postgresql 和时区方面遇到了困难。

Postgresql 服务器在 UTC 时区。我想执行一个查询，该查询返回具有时区偏移量的另一个时区中的日期。

我可以执行返回时区日期的查询，但我缺少时区偏移量

base=# SET timezone to 'utc';
SET
base=# select now();
             now              
------------------------------
 2018-04-20 14:58:22.68038+00
(1 row)

base=# SET timezone to 'Europe/Paris';
SET
base=# select now();
              now              
-------------------------------
 2018-04-20 16:58:29.614383+02
(1 row)


base=# SET timezone to 'utc';
SET
base=# select now() AT TIME ZONE 'Europe/Paris';
          timezone          
----------------------------
 2018-04-20 16:59:03.146917   -- missing timezone offset here
(1 row)

预期结果

base=# SET timezone to 'utc';
SET
base=# select now() AT TIME ZONE 'Europe/Paris'; --I'm missing something here, I guess
          timezone          
----------------------------
 2018-04-20 16:59:03.146917+02   -- That's what I want
(1 row)

你知道怎么做吗？

谢谢

Answer 1

如果您使用AT TIME ZONE，PG 会返回一个没有时区的时间戳。如果您将其转换回带时区的时间戳，您会得到预期的结果:

SELECT (NOW() AT TIME ZONE 'Europe/Paris')::TIMESTAMPTZ;

结果:

2018-04-20 18:15:26.165+02

---

如何在不设置时区的情况下获得+02？问题在于:NOW() AT TIME ZONE 'Europe/Paris' 返回指定时区的当前时间(没有偏移量)，并将其转换回 TIMESTAMPTZ为您提供基于 UTC 的当前时区(您的 PG session )的偏移量。由于“欧洲/巴黎”是 UTC 的 +02，如果您将时区设置为“欧洲/巴黎”，则您在 TIMESTAMPTZ 中获得的偏移量是 +02。如果您当前的时区设置为 UTC，则您的偏移量为 +00。

无论您的 PG session 的时区设置如何，如何在不显式设置时区的情况下获得偏移量？我想有一种更好的方法来获取偏移量，但在不知道的情况下，这是一种方法:计算偏移量，对其进行格式化，将其附加到不带时区的时间戳。

SELECT (NOW() AT TIME ZONE 'Europe/Paris')::TEXT || TO_CHAR(EXTRACT(Hour FROM NOW() AT TIME ZONE 'Europe/Paris' - NOW() AT TIME ZONE 'UTC'), 'FMSG00')

结果:2018-04-21 15:12:42.658+02，无论当前时区如何，我都会得到相同的结果。