How to specify UTC timezone for Spring Boot JPA Timestamp

Question

Environment

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

Problem

I am creating a Spring boot microservice that shares a Postgresql database with another service. The database is initialized externally (beyond our control), and the datetime column type used by another service is a timestamp without a time zone . Therefore, since I want all dates on db to be of the same type, the presence of this type is mandatory for JPA entity dates.

The way to map them to JPA entity objects is as follows:

@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 time in time, but I want to save it in UTC. This is because my time zone is set to Europe / Brussels by default, and JPA / JDBC converts my java.sql.Timestamp object to my time zone before putting it in the database.

Not perfect solutions found

• TimeZone.setDefault(TimeZone.getTimeZone("Etc/UTC")); it makes sense that I want to achieve, but it does not fit, because it does not apply to my service. That is, it will affect the entire JVM or current thread plus children.

• Running the application with -Duser.timezone=GMT also seems to be doing the job for a single instance of the running JVM. Therefore, a better solution than the previous one.

But is there a way to specify the time zone in the JPA / datasource / spring boot configuration?

Answer 1

The most appropriate solution to solve the problem is to use an AttributeConverter to convert Java 8 ZonedDateTime objects to ZonedDateTime objects so that they can be mapped to the PostgreSQL type timestamp without time zone .

The reason you need AttributeConverter is because Java 8 / Joda date time types are not yet compatible with JPA .

AttributeConverter as follows:

 @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 database timestamps that do not have timezone information in the form of ZonedDateTime objects that have a UTC timezone. This way, I keep the exact time that can be seen on db , regardless of the time zone in which my application runs .

Since toLocalDateTime() also applies the default time zone change, this AttributeConverter basically overrides the conversion used by the JDBC driver.

Do you really need to use timestamp without timezone ?

The reality is that if you store time date information in the time zone (even if it is UTC), the type timestamp without timezone PostgreSQL is the wrong choice. The correct data type to use would be timestamp with timezone , which includes time zone information. Read more about this topic here .

However, if for some reason you should use timestamp without timezone , I think the ZonedDateTime approach ZonedDateTime is a reliable and consistent solution.

Do you also serialize ZonedDateTime to JSON?

Then you are probably curious that you need at least version 2.6.0 jackson-datatype-jsr310 for serialization. More on this in this answer .

Answer 2

You can not. Eclipselink uses a single arg version of setTimestamp , delegating responsibility for handling the time zone for the driver, and the postgresql jdbc driver does not allow you to override the default time zone. The postgres driver even extends the client’s time zone to the session, so the default values ​​for the server side are also useless.

There are some hacking things that you could try to work around this problem, for example, write a JPA 2.1 AttributeConverter to shift your timestamps to the destination area, but they are ultimately doomed because your client’s time zone has daylight saving time adjustments, making several times ambiguous or unrepresentable.

You will need to set the default time zone on your client or go to your own SQL to set the timestamps as discarded rows.