How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate

(Last Updated On: January 4, 2018)

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:

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

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

10 thoughts on “How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate

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

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

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

  2. 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)?

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

    1. That’s expected because the TemporalType.DATE instructs Hibernate to call setDate which propagates only the date part and discards the time information.

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

Leave a Reply

Your email address will not be published. Required fields are marked *