PostgreSQL timestamptz 映射 Java OffsetDateTime 注意点

特别注意

如果直接将从PostgreSQL中映射过来的OffsetDateTime字段，如转为LocalDateTime需要特别注意。

OffsetDateTime offsetDateTime = OffsetDateTime.now(ZoneOffset.UTC);
System.out.println("offsetDateTime: " + offsetDateTime);
LocalDateTime localDateTime = OffsetDateTime.now(ZoneOffset.UTC).toLocalDateTime();
System.out.println("localDateTime: " + localDateTime);

输出

offsetDateTime: 2024-07-01T08:05:58.675885Z
localDateTime: 2024-07-01T08:05:58.676026

可以看到：PostgreSQL中映射过来的OffsetDateTime是零时区的时间字段。转为LocalDateTime时会直接丢掉时区信息。

原因：PostgreSQL底层存储的就是零时区的timestamptz。

Note
ZonedDateTime , Instant and OffsetTime / TIME WITH TIME ZONE are not supported. Also note that all OffsetDateTime instances will have be in UTC (have offset 0). This is because the backend stores them as UTC.

参考：https://jdbc.postgresql.org/documentation/query/#table51-supportedjava-8-date-and-time-classes

常用处理方法

1. timestamptz使用String、java.util.Date、java.sql.Timestamp接收
2. 将OffsetDateTime转为Date类型
3. 先将OffsetDateTime转为秒级时间戳，再用该时间戳转为LocalDateTime

PostgreSQL 接收 timestamptz

{
  "timeStr": "2024-07-01 15:52:17.399315+08",
  "timeOffsetDateTime": "2024-07-01T07:52:17.399315Z",
  "timeDate": "2024-07-01T07:52:17.399+00:00",
  "timeTimestamp": "2024-07-01T07:52:17.399+00:00"
}

相关文档

JDBC 4.2 规范

https://www.postgresql.org/docs/16/datatype-datetime.html