How to specify UTC timezone for Spring Boot JPA Timestamp

Question

Environment

Spring Boot Starter Data 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.

The way I map them on my 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 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.Timestamp object into my timezone before putting it into the database.

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=GMT seems to also do the job for a single instance of a running JVM. Therefore, a better solution than the previous one.

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

How do you "look at the database"? Perhaps it's just PostgreSQL adjusting the timestamp to your session's timezone? See this answer. — Adam Michalik
Thanks for answering @Adam, I just SELECT * FROM my_table. According to the answer you linked, "when you later display that timestamp, you get back what you entered literally". If I SELECT my_timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'UTC' FROM my_table I see the same result, which makes sense. Considering that, I would assume that it is not PostgreSQL adjusting the timezone, the adjustment seems to happen before the value gets into the db on the Java side. — oizulain

oizulain oizulain · Accepted Answer · 2017-01-16T12:33:10

The most appropriate workaround I could figure out for the problem is to use an AttributeConverter to convert Java 8 ZonedDateTime objects into java.sql.Timestamp objects, so that they can be mapped to the PostgreSQL timestamp without time zone type.

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

The AttributeConverter looks like this:

@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 not have timezone information as ZonedDateTime objects that have the UTC timezone. This way I keep the exact date time that can be seen on the db regardless of the timezone that my app runs in.

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

Do you really need to use `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 timezone PostgreSQL type is the wrong choice. The right data type to use would be timestamp with timezone which does include the timezone information. More on this topic here.

However, if for whatever reason, you must use the timestamp without timezone, I think the ZonedDateTime approach above is a robust and consistent solution.

Are you also serializing the `ZonedDateTime` to JSON?

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

How to specify UTC timezone for Spring Boot JPA Timestamp

2 Answers

Do you really need to use timestamp without timezone?

Are you also serializing the ZonedDateTime to JSON?

Do you really need to use `timestamp without timezone`?

Are you also serializing the `ZonedDateTime` to JSON?