Geek Answers Handbook

Convert date to sleep

I had a problem with wierd hibernate when setting a date field in an entity. The date is interpreted as UTC in the java program (I did System.out to make sure that the appointed date is in “UTC.” However, when the sleep mode is actually stored in the database, the date is converted to local time and saved) ex. the value is set in the object’s adjuster as "2009-09-09 00:08:08" - GMT, the actual value stored in the database, - "2009-09-08 08:08:08" is the US Eastern time. I can’t find out where and why this is happening, and how to prevent it. Thanks

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

+6
java date timezone hibernate jodatime
source share
3 answers

However, when sleep mode is actually stored in the database, the date is converted to local time and saved). the value was set in the object’s adjuster as "2009-09-09 00:08:08" - GMT, the actual value stored in the database, - "2009-09-08 08:08:08" is the US Eastern time.

Well, firstly, any type of column that you use to store your date in MySQL (TIMESTAMP or DATETIME) does not save the time zone . From Re: Timezone storage with datetime :

  • TIMESTAMP - seconds since 1970, sitting in 4 bytes. It is stored in GMT. That is, the TZ offset is applied when the value is stored, and then when reused. (...)

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

And secondly, if the error does not work], I understand that the conversion is supposed to be done either by the server or by the JDBC driver, depending on the settings of the server’s time zone , so that you do not receive conflicting data.

In both cases, I want to say that storing "2009-09-09 00:08:08" - GMT or "2009-09-08 08:08:08" - Eastern time US from Java should go to the same value in the database data.

However, it looks like another conversion is performed when it displays them. This begs the question: how did you actually check the value of the “saved date”. Is there a “problem” in your SQL client? In java code?

References

The MySQL documentation for DateTime says: “MySQL retrieves and maps the DATETIME values ​​to“ YYYY-MM-DD HH: MM: SS. ”This means mysql will convert“ milliseconds from the era ”to the above format. So now my question is, is timezone information also stored in mysql?

I updated my original answer (which was not completely accurate / comprehensive). Whether you use DATETIME or TIMESTAMP, the answer is no.

Another remark I made is that the “conversion” problem mentioned above only exists when I set the date in a Java application. If I create a mysql trigger to update / set the date using "UTC_TIMESTAMP ()", the date is displayed in the "UTC" format.

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

What I would like to know:

  • How did you "solve" the problem? With a SQL client or with Java?
  • What is the local time zone of the JVM?
  • What is the MySQL server timezone?
  • What is the version of MySQL JDBC driver?
  • Could you do a test using raw JDBC?
+3
source share

To process UTC dates in the database (for reading / writing), you can use this small open source DbAssist . It uses a custom UtcDateType to display java.util.Date fields in your entities, so that they are treated by Hibernate as UTC in the database. Since you are using JPA annotations, you should 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 need to make sure that you have the @EnableAutoConfiguration annotation before the application class. If you are using a different version of Hibernate, just go to the github wiki to find the correct patch version and installation guide. You can also learn more about the time zone shift issue in this article .

+1
source share

This behavior of the Joda Time Contrib is fixed in my Usertype project for Joda Time and JSR310. See http://usertype.sourceforge.net/ , which is practically a replacement for JodaTime sleep mode.

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

Hope this helps,

Chris

0
source share

More articles:


All Articles