Using Hibernate to persist dates in different time zones
Most of our Java projects share a quite common persistence stack: a bunch of Spring Data JPA repositories which store information in a relational database, using Hibernate as JPA provider.
When it comes to handling and storing dates, the common standard for server applications is to handle and store all dates in UTC format, or at least in some kind of standard and deterministic format such as ISO 8601. Recently though, we had a bit of a struggle getting one of our applications to work with dates.
The problem
On one side, this application relied on JVM defaults for timezones, in this case Europe/Madrid which can translate into GMT+1 or GMT+2 depending on the time of the year. On the other side, the same application exposes an API that receives dates in UTC format. This resulted in the application handling two kinds of dates: internal dates in GMT+1/+2 format and external dates in UTC format. These dates are internally represented as java.util.Calendar objects which are persisted to the database.
The mixing of timezones added to the particularities of the Calendar class being stored and retrieved by Hibernate made the CRUD operations involving dates inconsistent.
We are going to demonstrate this scenario using a simple entity class which holds two calendars. Notice we use Project Lombok’s fancy features to make our code less verbose.
Instances of this class are written and read by means of this Spring Data JPA repository where we’ve added two commodity methods for the sake of this demo (and also to override Hibernate’s cache):
To simulate what our application does, let’s take a look at this code:
In brief, we create two calendars belonging to the UTC and GMT+1 time zones respectively and we simultaneously write them to the database.
Using the logCalendar utility method above, we print each calendar’s epoch, hour of day and time zone:
Written GMT+1 Cal - epoch: 1551949200000 - hour of day: 10 - timezone: Europe/MadridWritten UTC Cal - epoch: 1551952800000 - hour of day: 10 - timezone: UTC
Notice the actual epochs are different since 10AM GMT+1 and 10AM UTC are not the same hour. But, what happens when we read the dates back from the database?
We have this output:
Read GMT+1 Cal - epoch: 1551949200000 - hour of day: 10 - timezone: Europe/MadridRead UTC Cal - epoch: 1551949200000 - hour of day: 10 - timezone: Europe/Madrid
What have just happened here? Everything seems to have worked fine for the GMT+1 calendar since it shares the same timezone as the JVM. However the UTC calendar’s been directly stored in the database and then loaded into a GMT+1 one with no hour transformation being performed. Keep in mind that 10AM UTC should result in 11AM GMT+1, so this behaviour is not what we should expect.
Configuring Hibernate’s JDBC timezone
As introduced by this post in the official Hibernate blog, since Hibernate 5.2.3 we can make use of the hibernate.jdbc.time_zone property to make the way dates are stored independent from the JVM’s default timezone. This seems a good fit for us, we can make all dates be stored back and forth in UTC format.
Let’s try the same code as before adding this bit of configuration to our Spring Boot Project:
spring.jpa.properties.hibernate.jdbc.time_zone=UTCAgain, we have the output for the write operation:
Written GMT+1 Cal - epoch: 1551949200000 - hour of day: 10 - timezone: Europe/MadridWritten UTC Cal - epoch: 1551952800000 - hour of day: 10 - timezone: UTC
And the output for the read:
Read GMT+1 Cal - epoch: 1551952800000 - hour of day: 11 - timezone: Europe/MadridRead UTC Cal - epoch: 1551952800000 - hour of day: 11 - timezone: Europe/Madrid
Well, this time things have gone better for the UTC Calendar, it’s been stored in the database, retrieved into a GMT+1 instance and the hour of day has been accordingly increased by one. Notice, as well, the epoch has been preserved between write and read.
Unluckily, the GMT Calendar has been also added an hour, notice the epoch has changed. Apparently this setting is not performing date conversion on both directions as promised by the author of the post, it seems to only be working on read operations.
In case you wonder what would happen if we set the configuration property to our default timezone, i.e. GMT+1, this is the output for such scenario:
Written GMT+1 Cal — epoch: 1551949200000 — hour of day: 10 — timezone: Europe/MadridWritten UTC Cal — epoch: 1551952800000 — hour of day: 10 — timezone: UTCRead GMT+1 Cal — epoch: 1551949200000 — hour of day: 10 — timezone: Europe/MadridRead UTC Cal — epoch: 1551949200000 — hour of day: 10 — timezone: Europe/Madrid
Well, the result is we are back at the starting point before any configuration was done.
We imagined that diving into Hibernate’s source code would be the best way to know what was actually happening. Doing it, we get to the code of the binder:
And to the code of the result extractor:
As we can see, write operations ignore the timezone configured by the property when dealing with calendars, as in our example. In this cases, the timezone of the calendar prevails over the configured one.
Conversely, on read operations there’s no distinction between calendars and other type of objects, so the dates stored in the database will always be interpreted according to the configured timezone.
We could argue this behaviour is misleading since we are configuring a transformation which, for calendars, only applies on the reading direction but for other types of dates applies bidirectionally. On the other side, the authors could answer that calendars have their own mechanism for indicating time zones, so, why ignore them?
Conclusion
With this examples in mind, the solution comes by defining a common timezone for all dates we are going to store in the database. If, as in our case, we handle calendars of different time zones, we have to make sure they are transformed to the common timezone before being persisted.
If the common timezone we’ve chosen happens to be the default of our JVM, no further customization is needed. Otherwise, we can make use of the hibernate.jdbc.time_zone property to make sure dates are properly retrieved.
In our last piece of code we chose GMT+1 as common timezone which happens to be the default timezone of our JVM. Accordingly, we make sure all calendars are transformed to GMT+1 before being persisted:
We can see the console output below:
Original UTC Cal - epoch: 1551952800396 - hour of day: 10 - timezone: UTCUTC Cal being written - epoch: 1551952800396 - hour of day: 11 - timezone: Europe/MadridGMT+1 Cal being written - epoch: 1551949200399 - hour of day: 10 - timezone: Europe/MadridRead GMT+1 Cal - epoch: 1551949200399 - hour of day: 10 - timezone: Europe/MadridRead UTC Cal - epoch: 1551952800396 - hour of day: 11 - timezone: Europe/Madrid
Notice how the UTC calendar changes from UTC to Europe/Madrid before being written, the hour of day increases but the epoch remains untouched. After the read operation we have the same calendars we wrote.
You can find the whole code of this post’s examples here.
