PostgreSQL timestamptz转本地时间环境不一致问题排查

问题核心 #

• 两个配置一致的环境：pre_prod、prod均为Ubuntu + PostgreSQL 12.18，存储的timestamptz值都是2024-03-27 07:00:00.000 +0530
• Java渲染结果差异：pre_prod显示2024-03-27 07:00:00.000（符合预期），prod却显示2024-03-27 12:30:00.000
• 已在系统、docker-compose、PostgreSQL、Java服务中配置Asia/kolkata（IST）时区，但pre_prod未做UTC转换直接返回原值，prod出现异常转换

PostgreSQL timestamptz的正确行为 #

PostgreSQL的timestamptz（带时区时间戳）内部统一以UTC存储，读取时会根据当前会话的timezone参数转换为对应时区的时间。举个例子：存入2024-03-27 07:00:00.000 +0530，内部实际存储的是UTC时间2024-03-27 01:30:00.000；当会话时区设为Asia/kolkata时，读取后应该转换回2024-03-27 07:00:00.000。

你的prod环境返回12:30:00，相当于把UTC时间2024-03-27 07:00:00再转IST（+0530），这说明prod的PostgreSQL会话时区可能被设成了UTC，或者Java服务的时区读取逻辑有问题，导致把UTC时间当成本地时间重复转换。

排查步骤 #

1. 验证PostgreSQL会话时区
   在两个环境分别执行以下SQL：

   SHOW timezone;
   SELECT current_setting('timezone');
   

   确认返回值都是Asia/kolkata或IST。如果prod返回UTC，说明时区配置未生效。

2. 检查PostgreSQL全局时区配置
   先找到配置文件路径，再查看时区参数：

   SHOW config_file; -- 获取postgresql.conf路径
   

   然后在宿主机或容器内执行：

   grep timezone /path/to/postgresql.conf
   

   确保timezone = 'Asia/kolkata'，修改后需要重启PostgreSQL才能生效。

3. 检查Docker容器内的系统时区
   如果PostgreSQL运行在Docker中，进入容器验证系统时区：

   docker exec -it <postgres容器ID> bash
   date
   cat /etc/timezone
   

   确认系统时区是Asia/kolkata，避免容器时区和宿主机不一致导致PostgreSQL读取错误时区。

4. 排查Java服务的时区处理

   • 确认Java服务启动时是否指定了时区参数：-Duser.timezone=Asia/Kolkata
   • 检查代码读取timestamptz的逻辑：PostgreSQL JDBC驱动默认会根据会话时区返回对应时区的Timestamp对象，如果代码里手动把它转成UTC再转IST，就会出现重复转换的问题。

关于转timestamp的建议 #

如果你的业务场景不需要跨时区兼容（比如存储的就是固定本地时间，不需要做时区转换），可以考虑把timestamptz转为timestamp。但要注意：

• timestamp不带时区，存储的是字面量时间，读取时直接返回存储的字符串，不会做任何时区转换。
• 如果未来可能有跨时区需求，timestamptz是更规范的选择，建议先把时区配置的问题排查清楚，不要直接切换字段类型。

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