PostgreSQL日期转带时区时间戳：如何实现用户时区独立？

问题原因 #

PostgreSQL的date类型本身不带时区信息，当你执行date_field AT TIME ZONE 'Europe/Warsaw'时，数据库会先按照当前会话的用户时区，把date_field转换成timestamp without time zone（比如用户时区是UTC时，2023-03-20会被转成2023-03-20 00:00:00 UTC），再转换为指定时区的timestamp with time zone，这就导致了不同用户时区下结果不一致。

解决方案 #

要彻底摆脱用户时区的影响，需要明确指定date_field对应的原始时区，确保转换过程完全独立于会话时区，以下两种方法都可以实现：

1. 显式构造无时区时间戳后转换
   先将date拼接为当天0点的无时区时间戳，再转换为指定时区的带时区时间戳：

UPDATE table SET timestamp_field = (date_field || ' 00:00:00')::timestamp without time zone AT TIME ZONE 'Europe/Warsaw';

2. 使用make_timestamptz函数直接构造
   通过函数直接提取日期的年、月、日，结合指定时区生成目标时间戳：

UPDATE table SET timestamp_field = make_timestamptz(
    EXTRACT(YEAR FROM date_field)::int,
    EXTRACT(MONTH FROM date_field)::int,
    EXTRACT(DAY FROM date_field)::int,
    0, 0, 0.0,
    'Europe/Warsaw'
);

效果验证 #

无论当前用户会话时区设为UTC还是'Europe/Warsaw'，执行上述语句后，timestamp_field都会得到2023-03-20 00:00:00+01，完全符合预期，且不受用户时区变化影响。

内容的提问来源于stack exchange，提问作者Tomasz Brzezina