How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
Dealing with time zones is always challenging. As a rule of thumb, it’s much easier if all date/time values are stored in the UTC format, and, if necessary, dealing with time zone conversions in the UI only.
This article is going to demonstrate how you can accomplish this task with JDBC and the awesome hibernate.jdbc.time_zone
configuration property.
Domain model
For our tests, we are going to use the following Book
entity which provides a java.sql.Timestamp
property to mark the date/time when the book was created:
@Entity @Table(name = "book") public class Book { @Id private Long id; private String title; @Column(name = "created_by") private String createdBy; @Column(name = "created_on") private Timestamp createdOn; //Getters and setters omitted for brevity }
Testing time
Assuming we want to persist the following book:
Book book = new Book(); book.setId(1L); book.setTitle("High-Performance Java Persistence"); book.setCreatedBy("Vlad Mihalcea"); book.setCreatedOn( new Timestamp( ZonedDateTime.of(2016, 8, 25, 11, 23, 46, 0, ZoneId.of("UTC") ).toInstant().toEpochMilli() ) ); assertEquals( 1472124226000L, book.getCreatedOn().getTime() ); entityManager.persist(book);
We explicitly set the createdOn
attribute to a UTC (GMT) timestamp, whose epoch milliseconds (e.g., 1472124226000) point to the expected date/time (e.g., Thu, 25 Aug 2016 11:23:46 GMT).
To see if there is any time zone drift, we are going to set the unit tests Timezone to US/Hawaii:
TimeZone.setDefault(TimeZone.getTimeZone("US/Hawaii"));
PostgreSQL
When executing the aforementioned entity persist on PostgreSQL, Hibernate generates the following INSERT statement:
INSERT INTO book ( created_by, created_on, title, id ) VALUES ( 'Vlad Mihalcea', '2016-08-25 01:23:46.0', 'High-Performance Java Persistence', 1 )
The INSERT
statement is expected since java.sql.Timestamp
, just like its base class (e.g. java.util.Date
), prints the underlying timestamp epoch milliseconds according to the local timezone, which is US/Hawaii in our case.
However, when fetching the timestamp from the database, we can see that the timestamp was actually saved according to the local timezone, and not relative to UTC:
Session session = entityManager.unwrap(Session.class); session.doWork(connection -> { try (Statement st = connection.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT TO_CHAR(created_on, 'YYYY-MM-DD HH24:MI:SS') " + "FROM book")) { while (rs.next()) { String timestamp = rs.getString(1); assertEquals( "2016-08-25 01:23:46", timestamp ); } } } });
If we load the entity back with Hibernate, we can see that the Timestamp
value epoch milliseconds hasn’t changed at all:
Book book = entityManager.find(Book.class, 1L); assertEquals(1472124226000L, book.getCreatedOn().getTime());
MySQL
The same goes for MySQL as well:
INSERT INTO book ( created_by, created_on, title, id ) VALUES ( 'Vlad Mihalcea', '2016-08-25 01:23:46.0', 'High-Performance Java Persistence', 1 )
The only difference is that we are now using the DATE_FORMAT
MySQL function, but the outcome is just the same as when using PostgreSQL:
Session session = entityManager.unwrap(Session.class); session.doWork(connection -> { try (Statement st = connection.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT DATE_FORMAT(created_on, '%Y-%m-%d %H:%i:%s') " + "FROM book")) { while (rs.next()) { String timestamp = rs.getString(1); assertEquals( "2016-08-25 01:23:46", timestamp ); } } } });
And, if we load the entity with Hibernate, we can see that the Timestamp
value has been translated back to the local JVM time zone, so the epoch millisecond value is the same like when we saved the entity:
Book book = entityManager.find(Book.class, 1L); assertEquals(1472124226000L, book.getCreatedOn().getTime());
Why?
What’s just happened? Why is the timestamp value 10 hours earlier than the initial value that we assigned for this column?
The problem lies in JDBC, not in Hibernate. By default, the TimestampType
Hibernate class is going to set the PreparedStatement
timestamp bind variable like this:
st.setTimestamp( index, timestamp );
And reading it from the ResultSet
is done as follows:
rs.getTimestamp( name )
However, both these two JDBC methods have an overloaded version which takes a Calendar
to set an explicit timezone:
PreparedStatement#setTimestamp(int parameterIndex, Timestamp x, Calendar cal)
ResultSet#getTimestamp(int columnIndex, Calendar cal)
The PreparedStatement
setTimestamp
Javadoc states that:
With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.
While for the ResultSet
getTimestamp
method it says that:
This method uses the given calendar to construct an appropriate millisecond value for the timestamp if the underlying database does not store timezone information.
So, because we haven’t specified an explicit timezone, the JDBC driver is going to assume that the Timestamp
epoch milliseconds should be treated according to the local timezone, which in our case is US/Hawaii.
One fix would be to change the default JVM time zone to UTC, but we don’t want that since the UI needs to be rendered according to our local US/Hawaii time zone.
The hibernate.jdbc.time_zone configuration property
In Hibernate 5.2.3, I addressed this problem with the HHH-11396 Jira issue which now allows you to pass the Timezone
in the SQL descriptors of TimestampType
and TimeType
so that the aforementioned overloaded JDBC methods (taking a timezone Calendar
) are used instead.
This way, we don’t need to change the JVM time zone, but we are able to instruct Hibernate to use whatever time zone we wish for the data access layer.
Setting the hibernate.jdbc.time_zone property using JPA
You can set this property in your JPA persistence.xml
configuration file like this:
<property name="hibernate.jdbc.time_zone" value="UTC"/>
Setting the hibernate.jdbc.time_zone property using Spring Boot
For Spring Boot, you can set this property in your application.properties
configuration file:
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
You should also set the DB to use UTC by default. Check out this article for more details.
PostgreSQL
When running our test case on PostgreSQL after setting this configuration property, we can see that the timestamp does not drift to a different time zone anymore:
Session session = entityManager.unwrap(Session.class); session.doWork(connection -> { try (Statement st = connection.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT TO_CHAR(created_on, 'YYYY-MM-DD HH24:MI:SS') " + "FROM book")) { while (rs.next()) { String timestamp = rs.getString(1); assertEquals( "2016-08-25 11:23:46", timestamp ); } } } });
Even if we load the entity back with Hibernate, we can see that the Timestamp
point to the same epoch millisecond snapshot:
Book book = entityManager.find(Book.class, 1L); assertEquals(1472124226000L, book.getCreatedOn().getTime());
MySQL
If you’re using the MySQL JDBC Connector/J prior to version 8, you need to set the
useLegacyDatetimeCode
connection property tofalse
as, otherwise, thehibernate.jdbc.time_zone
has no effect.The MySQL 8 Connector/J Driver has removed the
useLegacyDatetimeCode
configuration property, so you don’t need to set anything.
Now, if we execute the following SELECT query, we get the expected timestamp.
Session session = entityManager.unwrap(Session.class); session.doWork(connection -> { try (Statement st = connection.createStatement()) { try (ResultSet rs = st.executeQuery( "SELECT DATE_FORMAT(created_on, '%Y-%m-%d %H:%i:%s') " + "FROM book")) { while (rs.next()) { String timestamp = rs.getString(1); assertEquals( "2016-08-25 11:23:46", timestamp ); } } } });
And the same goes for when we load the entity with Hibernate:
Book book = entityManager.find(Book.class, 1L); assertEquals(1472124226000L, book.getCreatedOn().getTime());
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
And there is more!
You can earn a significant passive income stream from promoting all these amazing products that I have been creating.
If you're interested in supplementing your income, then join my affiliate program.
Conclusion
Dealing with time zones is always tricky, even when working with UTC or GMT time zones. Luckily, the hibernate.jdbc.time_zone
Hibernate configuration property is a great addition.
When working with MySQL, remember to set the useLegacyDatetimeCode
to false
, as, otherwise, the legacy date/time handling is going to use the database server timezone will just leave the timestamp as is.
Even if you provide the legacy useTimezone
or useJDBCCompliantTimezoneShift
connection properties, you won’t get the expected outcome, so it’s a good idea to use the new date/time handling logic, so better set the MySQL useLegacyDatetimeCode
property to false
.
