Hibernate date conversion

Question

I have a wierd hibernate related issue while setting a date field in an entity. The date is interpreted as UTC in the java program (i did a System.out to make sure the date assigned is in 'UTC'. However, when hibernate actually persists to the database, the date is converted to local time and stored) ex. the value has been set in the entity setter as "2009-09-09 00:08:08" - GMT the actual value persisted to the database is "2009-09-08 08:08:08" - eastern time US. I am unable to find out where and why this is happening and how to prevent it. Thanks

P.S. I am using joda date library and annotate the field with @org.hibernate.annotations.Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")

Answer 1

This behaviour of Joda Time Contrib is fixed in my project Usertype for Joda Time and JSR310. See http://usertype.sourceforge.net/ which is practically otherwise a drop in replacement for JodaTime Hibernate.

I have written about this issue: http://blog.jadira.co.uk/blog/2010/5/1/javasqldate-types-and-the-offsetting-problem.html

Hope this helps,

Chris

Answer 2

However, when hibernate actually persists to the database, the date is converted to local time and stored) ex. the value has been set in the entity setter as "2009-09-09 00:08:08" - GMT the actual value persisted to the database is "2009-09-08 08:08:08" - eastern time US.

Ok, first, whatever column type are you using to store your date in MySQL (TIMESTAMP or DATETIME), neither stores the time zone. From Re: Storing timezone with datetime:

• TIMESTAMP is seconds since 1970, sitting in 4 bytes. It is stored in GMT. That is, the TZ offset is applied as you store a value, then reapplied when you fetch it. (...)

• DATETIME is an 8-byte string of digits "yyyymmddhhmmss". (...)

And second, unless a buggy behavior, my understanding is that the conversion is supposed be done either by the server or by the JDBC driver depending on the the server time zone settings so that you don't get inconsistent data.

In both cases, my point is that storing "2009-09-09 00:08:08" - GMT or "2009-09-08 08:08:08" - eastern time US from Java should yield to the same value in the database.

However, it looks like a different conversion is done when displaying them. This begs the question: how did you actually check the value of the "persisted date". Does the "problem" occur in your SQL client? In Java code?

References

• 9.6. MySQL Server Time Zone Support
• 21.3.4.1. Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
• Bug #15604: TimeZone discarded storing java.util.Calendar into DATETIME

---

MySQL documentation for DateTime says "MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format". That means mysql converts the 'milliseconds since epoch' to the above format. So now my question becomes, is timezone info also stored in mysql?

I've updated my initial answer (which was not totally accurate/exhaustive). Whether you're using DATETIME or TIMESTAMP, the answer is no.

Another observation I made is, the above date 'conversion' issue exists only when Im setting the date in the Java application. If I create a mysql trigger to update/set date using 'UTC_TIMESTAMP()', the date is displayed in the 'UTC' time.

The UTC_TIMESTAMP() function always returns the current UTC date and time.

---

What I'd like to know is:

• How did you "reveal" the problem? With a SQL client or from Java?
• What is the local time zone of the JVM?
• What is the MySQL Server time zone?
• What is the version of the MySQL JDBC Driver?
• Could you do a test with raw JDBC?

Answer 3

In order to treat dates as UTC in the DB (for read/write), you can use this small open source library DbAssist. It uses a custom UtcDateType in order to map java.util.Date fields in your entities, so that they are treated by Hibernate as UTC in the DB. Since you are using JPA annotations, you would use the following dependency:

<dependency>
    <groupId>com.montrosesoftware</groupId>
    <artifactId>DbAssist-5.2.2</artifactId>
    <version>1.0-RELEASE</version>
</dependency>

Applying the fix is easy, for example, when using Spring Boot, you have to make sure that you have @EnableAutoConfiguration annotation before the application class. If you are using another Hibernate version, just refer to github wiki to find the proper version of the fix and the installation guide. You can also read more about the time zone shift issue in this article.