PostgreSQL与Hibernate执行相同date_part查询返回结果不一致

问题根因 #

你遇到的是JDBC会话时区和psql会话时区不一致导致的计算偏差，-180分钟恰好对应3小时的时区差，和你怀疑的时区问题方向一致，只是排查点有遗漏：

• psql执行查询时用的是本地客户端/数据库默认的会话时区，计算逻辑符合预期
• Hibernate通过JDBC连接数据库时，会话时区未对齐，导致current_timestamp取到的是JDBC会话对应时区的时间，和created字段存储的时区基准不一致，新作业的created时间按错误时区换算后甚至会晚于当前查询时间，最终得到负值。
• 换用extract方法无效的原因是：两种函数都是基于当前会话的时区做时间拆分，底层时间基准已经错误，上层函数调整不会改变结果。

验证方法 #

在你的原生查询中临时新增时区查询字段，确认两者差值：

@Query(value = "SELECT current_setting('timezone') as session_timezone, bind_name, type_name, max((24 * 60 * date_part('day', current_timestamp - created)) + (60 * date_part('hour', current_timestamp - created)) + (date_part('minute', current_timestamp - created))) FROM job WHERE state IN ('INITIAL', 'SUBMITTED') AND bind_name IS NOT NULL AND  type_name IS NOT NULL GROUP BY session_timezone, bind_name, type_name", nativeQuery = true)

分别打印Hibernate查询返回的session_timezone和psql执行select current_setting('timezone');的结果，两者必然不同。

解决方案 #

任选一种即可：

• 强制JDBC连接时区：在数据库连接字符串中增加时区参数，和数据库全局时区对齐，示例（以东八区为例）：

  jdbc:postgresql://xxx:5432/db_name?serverTimezone=Asia/Shanghai&useTimezone=true
  

• 查询时固定时区基准：修改SQL语句，统一使用标准时区计算，避免会话时区影响：

  -- 示例统一用东八区计算
  max((24 * 60 * date_part('day', now() AT TIME ZONE 'Asia/Shanghai' - created AT TIME ZONE 'Asia/Shanghai')) + (60 * date_part('hour', now() AT TIME ZONE 'Asia/Shanghai' - created AT TIME ZONE 'Asia/Shanghai')) + (date_part('minute', now() AT TIME ZONE 'Asia/Shanghai' - created AT TIME ZONE 'Asia/Shanghai')))
  

• 全局对齐JVM时区：Spring Boot启动参数增加JVM时区配置，和数据库时区一致：

  java -Duser.timezone=Asia/Shanghai -jar your_app.jar
  

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