postgresql 如何为 Spring Boot JPA 时间戳指定 UTC 时区

How to specify UTC timezone for Spring Boot JPA Timestamp

提问by oizulain

Environment

环境

• Spring Boot Starter Data JPA 1.4.2
• Eclipselink 2.5.0
• Postgresql 9.4.1211.jre7

• Spring Boot Starter 数据 JPA 1.4.2
• Eclipselink 2.5.0
• PostgreSQL 9.4.1211.jre7

Problem

问题

I am building a Spring Boot microservice that shares a Postgresql database with a different service. The database gets initialized externally (out of our control) and the datetime column type used by the other service is timestamp without time zone. Therefore, since I want all dates on the db to have the same type, having that type is a requirement for my JPA entity dates.

我正在构建一个与不同服务共享 Postgresql 数据库的 Spring Boot 微服务。数据库在外部初始化（不受我们控制），其他服务使用的 datetime 列类型是timestamp without time zone。因此，由于我希望数据库上的所有日期都具有相同的类型，因此我的 JPA 实体日期需要具有该类型。

The way I map them on my JPA entity objects is as follows:

我将它们映射到我的 JPA 实体对象上的方式如下：

@Column(name = "some_date", nullable = false)
private Timestamp someDate;

The problem is that when I create a Timestamp as follows:

问题是，当我按如下方式创建时间戳时：

new java.sql.Timestamp(System.currentTimeMillis())

and I look at the database, the timestamp contains my local timezone date time, but I want to store it in UTC. This is because my default Timezone is set to 'Europe/Brussels' and JPA/JDBC converts my java.sql.Timestampobject into my timezone before putting it into the database.

我查看数据库，时间戳包含我的本地时区日期时间，但我想将其存储在 UTC 中。这是因为我的默认时区设置为“欧洲/布鲁塞尔”，并且 JPA/JDBC 在将我的java.sql.Timestamp对象放入数据库之前将其转换为我的时区。

Found not ideal solutions

找到不理想的解决方案

• TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC"));has the effect that I want to achieve, but it's not suitable because it is not specific to my service. I.e. it will affect the whole JVM or the current thread plus children.

• Starting the application with -Duser.timezone=GMTseems to also do the job for a single instance of a running JVM. Therefore, a better solution than the previous one.

• TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC"));有我想要达到的效果，但不适合，因为它不是特定于我的服务。即它会影响整个 JVM 或当前线程加上孩子。

• 启动应用程序-Duser.timezone=GMT似乎也可以为正在运行的 JVM 的单个实例完成这项工作。因此，比上一个更好的解决方案。

But is there a way to specify the timezone within the JPA/datasource/spring boot configuration?

但是有没有办法在 JPA/datasource/spring boot 配置中指定时区？

采纳答案by oizulain

The most appropriate workaround I could figure out for the problem is to use an AttributeConverterto convert Java 8 ZonedDateTimeobjects into java.sql.Timestampobjects, so that they can be mapped to the PostgreSQL timestamp without time zonetype.

我能找到的最合适的解决方法是使用 anAttributeConverter将 Java 8ZonedDateTime对象转换为java.sql.Timestamp对象，以便它们可以映射到 PostgreSQLtimestamp without time zone类型。

The reason why you need an AttributeConverteris because the Java 8/Joda time date time types are not yet compatible with JPA.

您需要 an 的原因AttributeConverter是因为 Java 8/Joda 时间日期时间类型尚不与 JPA 兼容。

The AttributeConverterlooks like this:

该AttributeConverter如下所示：

@Converter(autoApply = true)
public class ZonedDateTimeAttributeConverter implements AttributeConverter<ZonedDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(ZonedDateTime zonedDateTime) {
        return (zonedDateTime == null ? null : Timestamp.valueOf(zonedDateTime.toLocalDateTime()));
    }

    @Override
    public ZonedDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
        return (sqlTimestamp == null ? null : sqlTimestamp.toLocalDateTime().atZone(ZoneId.of("UTC")));
    }

}

This allows me to read the database timestamps that do nothave timezone information as ZonedDateTimeobjects that have the UTCtimezone. This way I keep the exact date time that can be seen on the db regardless of the timezone that my app runs in.

这允许我读取没有时区信息的数据库时间戳作为ZonedDateTime具有UTC时区的对象。这样，无论我的应用程序运行在哪个时区，我都会保留可以在 db 上看到的确切日期时间。

Since toLocalDateTime()also applies the system's default timezone conversion, this AttributeConverterbasically cancels out the conversion applied by the JDBC driver.

由于toLocalDateTime()还应用了系统的默认时区转换，这AttributeConverter基本上取消了 JDBC 驱动程序应用的转换。

Do you really need to use timestamp without timezone?

你真的需要使用timestamp without timezone吗？

The reality is that if you are storing date time information on a time zone (even if it is UTC), the timestamp without timezonePostgreSQL type is the wrong choice. The right data type to use would be timestamp with timezonewhich does include the timezone information. More on this topic here.

现实情况是，如果您在时区（即使是 UTC）上存储日期时间信息，那么timestamp without timezonePostgreSQL 类型是错误的选择。要使用的正确数据类型timestamp with timezone是包含时区信息的数据类型。更多关于这个主题的信息在这里。

However, if for whatever reason, you mustuse the timestamp without timezone, I think the ZonedDateTimeapproach above is a robust and consistent solution.

但是，如果出于某种原因，您必须使用timestamp without timezone，我认为上述ZonedDateTime方法是一个强大且一致的解决方案。

Are you also serializing the ZonedDateTimeto JSON?

您是否也ZonedDateTime将 JSON序列化？

Then you are probably interested in the fact that you need at least version 2.6.0of the Hymanson-datatype-jsr310dependency for the serialization to work. More on that in this answer.

那么你可能感兴趣的是，你需要至少版本的事实2.6.0中的Hymanson-datatype-jsr310用于串行化工作的依赖性。更多关于这个答案。

回答by teppic

You can't. Eclipselink uses the single arg version of setTimestamp, delegating responsibility for time zone handling to the driver, and the postgresql jdbc driver doesn't allow the default time zone to be overridden. The postgres driver even propagates the client time zone to the session, so server side defaults will be of no use to you either.

你不能。Eclipselink 使用单个 arg 版本setTimestamp，将时区处理的责任委托给驱动程序，而 postgresql jdbc 驱动程序不允许覆盖默认时区。postgres 驱动程序甚至将客户端时区传播到会话，因此服务器端默认值对您也没有用处。

There are some hackish things you could try to work around the problem, for example writing a JPA 2.1 AttributeConverterto shift your timestamps into the destination zone, but ultimately they're doomed because your client time zone has daylight savings adjustments, making some times ambiguous or unrepresentable.

您可以尝试解决一些骇人听闻的事情，例如编写 JPA 2.1AttributeConverter以将您的时间戳转换到目标区域，但最终它们注定要失败，因为您的客户端时区有夏令时调整，使某些时间变得模棱两可或无法代表。

You'll have to set the default time zone on your client, or drop into native SQL to set timestamps as strings with casts.

您必须在客户端上设置默认时区，或者使用本机 SQL 将时间戳设置为带有强制转换的字符串。