Environment
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?
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.
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.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments