使用ZonedDateTime查询timestamp(6)类型数据异常求助

问题场景 #

定义了如下Spring Data JPA Repository方法：

@Query(value="select * from users where created_at < :date", nativeQuery=true)
List<Users> getUsers(@Param("date") ZonedDateTime date);

数据库created_at字段类型为timestamp(6)，传入ZonedDateTime.now()时查询正常，但传入ZonedDateTime.now().minusYears(1)这类修改后的日期时查询失效，尝试过Instant、Calendar、String、Date类型参数，结果一致。

可能的原因及解决思路 #

• 时区不匹配问题
  数据库的timestamp(6)通常不带时区信息，而ZonedDateTime包含时区。当传入修改后的日期时，时区转换可能出现偏差，导致实际传入数据库的时间和预期不符。比如数据库默认时区为UTC，应用时区为东八区，修改后的日期转换后被错误调整，使得查询条件不成立。
  解决：

  1. 统一应用与数据库的时区配置，比如均设置为UTC或本地时区；
  2. 将ZonedDateTime转换为不带时区的LocalDateTime再传入，因为timestamp(6)与Java的LocalDateTime类型更匹配：

     @Query(value="select * from users where created_at < :date", nativeQuery=true)
     List<Users> getUsers(@Param("date") LocalDateTime date);
     

     调用时通过ZonedDateTime.now().minusYears(1).toLocalDateTime()完成转换。

• 参数绑定的类型转换兼容性问题
  原生查询中，JPA对ZonedDateTime的类型转换可能存在版本兼容性问题，尤其是修改后的日期可能触发了异常转换逻辑。部分版本的Hibernate在处理非当前时间的ZonedDateTime时，转换为数据库timestamp的方式有误。
  解决：

  1. 若使用Date类型参数，可显式指定类型：@Param("date") @Temporal(TemporalType.TIMESTAMP) Date date，但更推荐使用LocalDateTime；
  2. 手动将日期转换为数据库兼容的字符串格式（如ISO-8601），作为String参数传入，同时在SQL中用数据库函数转换：

     @Query(value="select * from users where created_at < TO_TIMESTAMP(:date, 'YYYY-MM-DD HH24:MI:SS.FF')", nativeQuery=true)
     List<Users> getUsers(@Param("date") String date);
     

     调用时通过ZonedDateTime.now().minusYears(1).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSS"))生成符合要求的字符串。

• 数据库时间精度差异问题
  timestamp(6)支持微秒精度，部分日期类型转换时可能丢失精度，导致查询条件中的时间与数据库存储的时间存在细微差异。比如数据库记录的时间是2023-10-01 12:00:00.123456，而传入的参数是2023-10-01 12:00:00.000000，此时若记录时间大于参数，就不会被查询到。
  解决：

  1. 确保传入的日期参数保留微秒精度，比如使用LocalDateTime时注意精度保留；
  2. 适当调整SQL条件，比如改用created_at <= :date，或根据数据库语法截断字段精度：DATE_TRUNC('second', created_at) < :date。

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