java 休眠日期转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3655036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate date conversion
提问by missionE46
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
在实体中设置日期字段时，我遇到了一个奇怪的休眠相关问题。日期在java程序中被解释为UTC（我做了一个System.out以确保分配的日期是'UTC'。但是，当hibernate实际上坚持到数据库时，日期被转换为本地时间并存储）例如. 该值已在实体设置器中设置为“2009-09-09 00:08:08” - GMT 保存到数据库的实际值为“2009-09-08 08:08:08” - 美国东部时间。我无法找出发生这种情况的地点和原因以及如何预防。谢谢
P.S. I am using joda date library and annotate the field with @org.hibernate.annotations.Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")
PS 我正在使用 joda 日期库并使用 @org.hibernate.annotations.Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime") 注释该字段
采纳答案by Pascal Thivent
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.
但是，当 hibernate 实际上持久化到数据库时，日期被转换为本地时间并存储）例如。该值已在实体设置器中设置为“2009-09-09 00:08:08” - GMT 保存到数据库的实际值为“2009-09-08 08:08:08” - 美国东部时间。
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:
好的，首先，无论您使用什么列类型在 MySQL 中存储日期（TIMESTAMP 或 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". (...)
TIMESTAMP 是自 1970 年以来的秒数，占 4 个字节。它以格林威治标准时间存储。也就是说，TZ 偏移量在您存储值时应用，然后在您获取它时重新应用。(...)
DATETIME 是一个 8 字节的数字串“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 zonesettings so that you don't get inconsistent data.
其次，除非有错误行为，否则我的理解是转换应该由服务器或 JDBC 驱动程序完成，具体取决于服务器时区设置，这样您就不会得到不一致的数据。
In both cases, my point is that storing "2009-09-09 00:08:08" - GMTor "2009-09-08 08:08:08" - eastern time USfrom Javashould yield to the same valuein the database.
在这两种情况下，我的观点是，存储“2009-09-09 0时08分08秒” - GMT或“2009-09-08 8时08分08秒” -东部时间美国从Java应该得到相同的值在数据库。
However, it looks like a different conversion is done when displayingthem. 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?
但是，显示它们时似乎完成了不同的转换。这就引出了一个问题：您实际上是如何检查“持久日期”的值的。“问题”是否出现在您的 SQL 客户端中？在 Java 代码中？
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
- 9.6. MySQL 服务器时区支持
- 21.3.4.1. Connector/J 的驱动程序/数据源类名称、URL 语法和配置属性
- 错误 #15604：TimeZone 放弃将 java.util.Calendar 存储到 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?
DateTime 的 MySQL 文档说“MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索并显示 DATETIME 值”。这意味着 mysql 将“自纪元以来的毫秒数”转换为上述格式。所以现在我的问题变成了，时区信息是否也存储在 mysql 中？
I've updated my initial answer (which was not totally accurate/exhaustive). Whether you're using DATETIME or TIMESTAMP, the answer is no.
我已经更新了我的初始答案（不完全准确/详尽）。无论您使用的是 DATETIME 还是 TIMESTAMP，答案是否定的。
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.
我所做的另一个观察是，仅当我在 Java 应用程序中设置日期时才存在上述日期“转换”问题。如果我使用 'UTC_TIMESTAMP()' 创建一个 mysql 触发器来更新/设置日期，则日期显示在 'UTC' 时间。
The UTC_TIMESTAMP()function alwaysreturns the current UTCdate and time.
该UTC_TIMESTAMP（）函数总是返回当前UTC日期和时间。
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?
- 你是如何“揭示”问题的？使用 SQL 客户端还是来自 Java？
- JVM 的本地时区是什么？
- MySQL 服务器时区是什么？
- MySQL JDBC 驱动程序的版本是什么？
- 你能用原始 JDBC 做一个测试吗？
回答by Wertr Trotz
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 UtcDateTypein order to map java.util.Datefields 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:
为了将 DB 中的日期视为 UTC（用于读/写），您可以使用这个小型开源库DbAssist。它使用自定义UtcDateType来映射java.util.Date实体中的字段，以便 Hibernate 将它们视为数据库中的 UTC。由于您使用的是 JPA 注释，您将使用以下依赖项：
<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 @EnableAutoConfigurationannotation before the application class. If you are using another Hibernate version, just refer to github wikito 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.
应用修复很容易，例如，在使用 Spring Boot 时，您必须确保@EnableAutoConfiguration在应用程序类之前有注解。如果您使用的是其他 Hibernate 版本，只需参考github wiki即可找到正确的修复版本和安装指南。您还可以在本文中阅读有关时区偏移问题的更多信息。
回答by Chris Pheby
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.
Joda Time Contrib 的这种行为在我的 Joda Time 和 JSR310 项目用户类型中得到了修复。请参阅http://usertype.sourceforge.net/，否则它实际上是 JodaTime Hibernate 的替代品。
I have written about this issue: http://blog.jadira.co.uk/blog/2010/5/1/javasqldate-types-and-the-offsetting-problem.html
我写过关于这个问题的文章：http: //blog.jadira.co.uk/blog/2010/5/1/javasqldate-types-and-the-offsetting-problem.html
Hope this helps,
希望这可以帮助，
Chris
克里斯
