PostgreSQL timestamptz 映射 Java OffsetDateTime 注意点

PostgreSQL Java 时区 About 1,284 words

特别注意

如果直接将从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使用Stringjava.util.Datejava.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

Views: 223 · Posted: 2024-07-01

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓
Today On History
Browsing Refresh