How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate
If you are trading Stocks and Crypto using Revolut, then you are going to love RevoGain!
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
useLegacyDatetimeCodeconnection property tofalseas, otherwise, thehibernate.jdbc.time_zonehas no effect.The MySQL 8 Connector/J Driver has removed the
useLegacyDatetimeCodeconfiguration 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.
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.
