sql - 如何使用 AT TIME ZONE 选择带有偏移量的时间戳

Question

我在 PostgreSQL 中摆弄时间戳，读了一篇有趣的 Blog关于这种行为，但没有解决以下问题的线索:

如何从另一列中选择带有 AT TIME ZONE 的“带有时区的时间戳”列，包括一步中与 UTC 的偏移量？

我在 PostgreSQL 10 中有一个示例表:

CREATE TABLE public.test_tz
(
    id serial,
    in_zone_timestamp timestamp without time zone,
    in_zone character varying COLLATE pg_catalog."default",
    CONSTRAINT test_tz_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

PostgreSQL的设置是

timezone=localtime

服务器运行在“欧洲/柏林”

$ date --> Fri Mar 9 12:15:23 CET 2018

时间戳列应该使用这种困惑的语法存储:

INSERT INTO public.test_tz ( in_zone_timestamp, in_zone) VALUES
('2017-02-01 00:00:00' AT TIME ZONE current_setting('TIMEZONE') AT TIME ZONE 'Asia/Singapore', 'Asia/Singapore' );

如果有人知道更好的方法，请不要犹豫!另一种解决方案是使用 '2017-02-01 00:00:00-08' 作为值，但我不知道偏移值。

我想存储本地时间戳，例如:“用户于 2017-02-01 00:00:00 在新加坡按下按键”。

如果我询问数据库:欧洲的哪个时间，当新加坡的用户按下按键时，我得到:

SELECT in_zone_timestamp FROM public.test_tz;
--> '2017-01-31 17:00:00+01'

这似乎没问题，因为新加坡有 +8 小时的偏移。

如果我想知道新加坡的时间，我会使用:

SELECT in_zone_timestamp AT TIME ZONE in_zone FROM public.test_tz;
--> '2017-02-01 00:00:00'

这也没关系，但是，它不会返回“UTC”的偏移量，所以我看不到这个时间戳不是我的本地时间!

我尝试了 AT TIME ZONE 或转换为 timestamptz 的一些组合，但结果不是我想要的。我期望的结果是这样的:

--> '2017-02-01 00:00:00+08'

在这里，我只看到一种解决方案，手动连接/转换/操作结果并手动添加偏移量，但这是唯一的方法吗？

抱歉，如果我解释这个问题有点过于复杂，希望有人能理解我的想法。

提前致谢

Answer 1

让 PostgreSQL 自动将时间戳转换为 2017-02-01 00:00:00+08 之类的字符串的唯一方法是更改​​ session 时区:

SET timezone = 'Asia/Singapore';

您可以使用SET LOCAL仅在事务持续时间内更改设置。

如果您不希望这样，您可以使用如下查询获取偏移量:

SELECT TIMESTAMP '2000-01-01 00:00:00' AT TIME ZONE 'UTC'
     - TIMESTAMP '2000-01-01 00:00:00' AT TIME ZONE 'Asia/Singapore';

 ?column? 
----------
 08:00:00
(1 row)