PostgreSQL调用存储过程时now()返回时间与时区不符问题

问题根因 #

• now() 函数返回的是timestamp with time zone（带时区时间戳）类型，当写入timestamp without time zone（不带时区时间戳）字段时，PostgreSQL会自动基于当前会话的时区将其转换为本地时间后丢弃时区信息存储。你的数据库默认时区为US/Pacific（夏令时UTC-7，非夏令时UTC-8），你所在时区为GMT+7（Asia/Bangkok），刚好存在13-14小时的时差，和你遇到的现象完全吻合。
• 你手动执行的set timezone='Asia/Bangkok'是会话级配置，仅对当前连接生效。大部分数据库客户端会在建立连接时自动读取操作系统区域设置配置会话时区，你的操作系统区域设置为美国（英语），会导致客户端自动覆盖时区配置，因此修改后写入的时间仍然有时差。
• 不带时区的时间戳本身不存储时区信息，修改数据库时区不会回溯更新历史存储的时间数据，因此旧数据不会自动变更。

解决步骤 #

1. 配置永久生效的默认时区 #

• 如果你有数据库服务器配置修改权限：编辑postgresql.conf文件，将timezone参数修改为timezone = 'Asia/Bangkok'，保存后执行SELECT pg_reload_conf();重载配置即可生效，无需重启服务。
• 如果你仅有当前数据库用户的权限：执行ALTER ROLE 你的数据库用户名 SET timezone TO 'Asia/Bangkok';，后续该用户建立的所有新连接都会默认使用GMT+7时区。

2. （推荐）修改时间字段为带时区类型 #

将LastUpdatedDate字段修改为timestamp with time zone类型，该类型会统一存储UTC时间，查询时自动根据当前会话时区转换为对应本地时间，从根本上避免时区混乱：

ALTER TABLE trbankd ALTER COLUMN LastUpdatedDate TYPE timestamp with time zone;

3. （可选）强制写入指定时区时间，不受会话配置影响 #

如果你需要保留timestamp without time zone字段类型，可以修改存储过程和字段默认值，写入时明确指定时区，彻底规避会话时区的影响：

• 修改存储过程中更新逻辑：将LastUpdatedDate=now()替换为LastUpdatedDate=now() AT TIME ZONE 'Asia/Bangkok'
• 修改字段默认值：执行ALTER TABLE trbankd ALTER COLUMN LastUpdatedDate SET DEFAULT now() AT TIME ZONE 'Asia/Bangkok';

4. 修正历史数据（按需操作） #

如果需要修正之前写入的错误历史时间，可以执行以下语句批量更新（操作前请务必备份数据）：

UPDATE trbankd 
SET LastUpdatedDate = LastUpdatedDate AT TIME ZONE 'US/Pacific' AT TIME ZONE 'Asia/Bangkok';

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