pg 的毫秒级时间戳转换

[yustation]

select to_timestamp('1672531200000'::bigint / 1000) AT TIME ZONE 'UTC-8';

结果为 2023-01-01 08:00:00

select to_timestamp('1672531200000'::bigint / 1000) AT TIME ZONE 'UTC+8';

结果为 2022-12-31 16:00:00

为什么'UTC-8'才是北京时间?

[wangee]

pg 当中区分 timestamptz 和 timestamp ，可以使用`SHOW timezone;`看看。

[sagaxu]

因为 select '2023-01-01 08:00:00+08' AT TIME ZONE 'UTC+8' 是'2022-12-31 16:00:00'

[yustation]

@wangee Asia/Shanghai ； to_timestamp 返回值是 timestamptz ，是我误解为 timestamp 了，谢谢

[yustation]

@sagaxu 谢谢！点醒我了

[wangee]

我更正一下我上面的回复，这个问题来自于 PostgreSQL 中 Time Zone 的格式与 ISO-8601 以及其他 PostgreSQL 中显示的不一致：UTC+8 在 Time Zone 配置中代表 UTC 西边 8 小时，而不是常规的向东 8 小时。参考文档： https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

PostgreSQL 中，timestamp 和 timestamptz 的底层都是 UTC 时间，仅仅是输出形式不同。

to_timestamp 函数会将 Unix 时间戳转换为 timestamptz ，等价于 timestamp with time zone '2023-01-01 08:00:00+08'。

at time zone 函数在处理 timestamptz 的时候，输出的转换后时区的 timestamp 。在上面的例子中，将会转换到 UTC+8 时区之后，显示在 UTC+8 显示的时间。参考文档： https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

因此需要关注 PostgreSQL 中对于 Time Zone 的表示，一共有三种，参考文档： https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES 。其中对于第三种，就是我开头第一段写的，和常用的不一致。

将 OP 的查询语句中的时区改为 Asia/Shanghai ，就是 OP 想要的北京时间：2023-01-01 08:00:00 了。

之前回复的牛头不对马嘴，抱歉

[yustation]

select to_timestamp('1672531200000'::bigint / 1000) AT TIME ZONE 'Asia/Shanghai'; -- 2023-01-01 08:00:00

确实如此，受教了！

[yustation]

@wangee 谢谢大佬！