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?
You cannot “ get a TimeZone ID from a certain TimeStamp”, that is impossible. Your count-from-epoch was made while accounting for a certain time zone, usually UTC. If must know that intended zone used in creating that count-from-epoch, it cannot be deduced.
For timestamp with time zone , the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT ). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.
To set the timezone for a given JDBC connection, navigate to the Advanced tab and select the timezone from the dropdown menu. By default, UTC is selected.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With