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 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.