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 conversations 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 like 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 is 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.
hibernate.jdbc.time_zone
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.
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 milisecond snapshot:
Book book = entityManager.find(Book.class, 1L); assertEquals(1472124226000L, book.getCreatedOn().getTime());
MySQL
For MySQL, by default, the hibernate.jdbc.time_zone
has no effect unless we provide the useLegacyDatetimeCode
connection property to false
.
After we set the useLegacyDatetimeCode
configuration property, we get the expected outcome:
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 us ethe new date/time handling logic, so better set the MySQL useLegacyDatetimeCode
property to false
.
That’s a cool feature! It seems we can remove our UTCCalendarUserType and just use this property. I just tested it and found a little bug HHH-12254
Thanks, but that’s not an issue. It’s due to how Java
Date#toString()
works.Hi Vlad,
it is not entirely clear to me what type the createdOn column has (I’m focussing on PostgreSQL right now), but I think it is important for your example.
From your examples, I assume that it is a “timestamp without time zone”? This would explain that the string “2016-08-25 01:23:46” from JDBC is written into the database without any adjustment.
However, every PostgreSQL session has an associated timezone (see it with “show timezone”). If you connect from a Java application, it is usually the same as the JVM default timezone. If the column was of type “timestamp with timezone”, PostgreSQL would consider the input string “2016-08-25 01:23:46” in that timezone, and convert it back to UTC (i.e 2016-08-25 11:23:46) for internal storage.
A similar procedure applies for reading: PostgreSQL will convert the value “2016-08-25 11:23:46” into the session timezone, so JDBC will receive “2016-08-25 01:23:46”, which is then converted back to “2016-08-25 11:23:46” with the rs.getTimestamp() method.
This way, the only part of the application which sees localized timestamp is the JDBC driver.
This could be used as a workaround if you cannot set the JVM default timezone and cannot upgrade your Hibernate version, or are using a provider other than Hibernate.
I used Timestamp without TZ. You can use the TZ types from Hibernate too, but you need to be sure the local TZ is set properly every time.
Afaik, the “with TZ” types free you from having to set a specific timezone anywhere. You might even use a different timezone when you connect next time.
It is the “without TZ” types that force you to handle timezones yourself, as is done here with the hibernate.jdbc.time_zone property.
With TZ requires you to set the local TZ at the database session level. You have to make sure each client set it properly and clears it before returning the Connection to the pool. And what about other databases which lack this TZ type?
Hi Vlad,
If I want my application to always store and read dates in UTC timezone is it not enough to call TimeZone.setDefault(TimeZone.getTimeZone(“UTC”)); on application startup? Why do I need hibernate.jdbc.time_zone? Will it force the jdbc driver to use UTC globally without this property (my hibernate version is lower than 5.2.3)?
Just upgrade to benefit from this configuration property.
Hello Vlad,
very interesting article! Tried to apply your solution on a slightly different case where I have:
@Temporal(TemporalType.DATE)
@Column(name = “start_date”)
private Date startDate;
instead of a timestamp. I set hibernate.jdbc.time_zone = true but nothing happens. For instance I want to see 2017-04-04 15:02:32+03 persisted, but what I get is 2017-04-04 00:00:00+03 instead.
Any ideas on why this happens? Thanks a lot.
That’s expected because the
TemporalType.DATE
instructs Hibernate to callsetDate
which propagates only the date part and discards the time information.I see. Since in my postgres DB I declare startDate as timestamp with time zone, to make this work (get both date and time) I should do something like this:
@Temporal(TemporalType.TIMESTAMP)
@Column(name = “start_date”)
private Date startDate;
and alter my DB column to datetime?
If you need a timestamp with both date and time, then yes.