H2中的DATETIME值与从Java/Kotlin插入的MySQL数据库之间不匹配 [英] Mismatch between DATETIME values in H2 and MySQL databases inserted from Java/Kotlin

问题描述

TLDR:

如何使用Java Hibernate始终将正确的UTC日期时间值保存到H2和MySQL数据库的DATETIME类型的字段中?

How to always save correct UTC date time value into the field of DATETIME type of both H2 and MySQL databases with Java Hibernate?

完整上下文:

我在数据库中有一个带有DATETIME字段的表，我想在其中插入行:

I have a table with DATETIME field in the database and I want to insert rows where:

• 默认情况下(未提供任何值时)将存储当前UTC时间
• 或如果给出了UTC日期时间，则应将其存储为 其他时区转换.

• by default (when no value is given) will be stored current UTC time
• or if the UTC date time is given, it should be stored without additional timezone conversions.

它必须在本地H2数据库以及Docker内部的本地mysql和外部AWS RDS MySQL实例上运行的问题.

The problem that it has to run on local H2 database as well as on local mysql inside Docker and on external AWS RDS MySQL instance.

我很难在所有三个实例中正确保存日期时间.

And I'm having a hard time making datetime to be saved correctly in all 3 instances.

到目前为止，要么是本地实例，要么是aws mysql实例正在获取正确的值，但是本地H2却获取了错误的值；或者，当本地H2获得正确的值但MySQL实例获取了错误的值时，反之亦然.

So far it's either local and aws mysql instances are getting correct values but local H2 gets wrong value, or other way around, when local H2 gets correct value but MySQL instances are getting wrong values.

这是我的Kotlin代码的简短片段.

Here are shortened snippets of kotlin code that I have.

适用于H2但不适用于Docker和AWS中的MySQL的代码:

@Entity
data class SomeEntity(
    val createdAt: LocalDateTime = LocalDateTime.now(Clock.systemUTC())
    // If createdAt is not explicitly given when saving new entry in db, the default value will be used
    // and H2 will get correct value of '2019-03-28 12:36:56',
    // but it will be wrong for MySQL, it will get '2019-03-28 11:36:56'
)

val dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd H:mm:ss")

createdAt = LocalDateTime.parse("2012-11-30 16:13:21", dateTimeFormatter)
// In this case when createdAt is explicitly given when saving new entry in db,
// H2 gets correct value '2012-11-30 16:13:21', 
// but MySQL DBs will get wrong value of '2012-11-30 17:13:21'

在Docker和AWS中适用于MySQL但不适用于H2的代码:

@Entity
data class SomeEntity(
    val createdAt: Date = Date()
    // If createdAt is not explicitly given when saving new entry in db, the default value will be used
    // and MySQL DBs will get correct value of '2019-03-28 12:36:56'
    // but it will be wrong for H2 as it will get '2019-03-28 13:36:56' (my current local time instead of UTC)
)

val dateTimeFormatter = SimpleDateFormat("yyyy-MM-dd H:mm:ss")
dateTimeFormatter.timeZone = TimeZone.getTimeZone("UTC")

createdAt = dateTimeFormatter.parse("2012-11-30 16:13:21")
// In this case when createdAt is explicitly given when saving new entry in db,
// MySQL DBs will get correct value '2012-11-30 16:13:21', 
// but H2 will get wrong value of '2012-11-30 17:13:21'

它运行于: Spring Boot 2.1.3 ， Hibernate Core 5.3.7 ， MySQL 8.0.13 ， H2 1.4 .197

我在网上和堆栈溢出中都看到了很多问题，但是不幸的是，没有一种解决方案可以解决我的问题.

I've seen bunch of questions online and also on stackoverflow but unfortunately none of the solutions could fix my problem.

更新

在使用多种方法进行了额外的调试之后，通过查看Hibernate，H2和MySQL的日志，看起来UTC时间在H2和MySQL之间完全相反.

After additional debugging with multiple approaches, looking through the logs of Hibernate, H2 and MySQL, it looks like UTC time is treated exactly opposite way between H2 and MySQL.

保存到本地H2:

• [错误]使用Date，当UTC为 09:55 时，Hibernate记录值"2019年3月29日 10:55 :09 CET"，将其保存表示为"2019-03-29 10:55 :09.412".
• [错误]使用Instant，当UTC为 16:48 时，Hibernate记录值"2019-03-28T 16:48 :18.270Z"，它是保存为"2019-03-28 17:48 :18.27".
• [错误]使用OffsetDateTime，当UTC为 10:11 时，Hibernate记录的值为"2019-03-29T 10:11 :30.672Z"，保存为"2019-03-29 11:11 :30.672".
• [正确]使用LocalDateTime，当UTC为 16:50 时，Hibernate记录值"2019-03-28T 16:50 :20.697"，将其保存表示为"2019-03-28 16:50 :20.697".

• [wrong] using Date, when UTC is 09:55, Hibernate logs value "Fri Mar 29 10:55:09 CET 2019", it's saved as "2019-03-29 10:55:09.412".
• [wrong] using Instant, when UTC is 16:48, Hibernate logs value "2019-03-28T16:48:18.270Z", it's saved as "2019-03-28 17:48:18.27".
• [wrong] using OffsetDateTime, when UTC is 10:11, Hibernate logs value "2019-03-29T10:11:30.672Z", it's saved as "2019-03-29 11:11:30.672".
• [correct] using LocalDateTime, when UTC is 16:50, Hibernate logs value "2019-03-28T16:50:20.697", it's saved as "2019-03-28 16:50:20.697".

在本地docker中保存到MySQL:

• [正确]使用Date，当UTC为 09:51 时，Hibernate记录值"2019年3月29日 10:51 :56 CET"，它将保存为"2019-03-29 09:51 :56.519".
• [正确]使用Instant，当UTC为 09:38 时，Hibernate记录的值为"2019-03-29T 09:38 :59.172Z"，保存为"2019-03-29 09:38 :59.172".
• [正确]使用OffsetDateTime，当UTC为 10:14 时，Hibernate记录值"2019-03-29T 10:14 :22.658Z"，保存为"2019-03-29 10:14 :22.658".
• [错误]使用LocalDateTime，当UTC为 16:57 时，Hibernate记录值"2019-03-28T 16:57 :35.631"，将其保存表示为"2019-03-28 15:57 :35.631".

• [correct] using Date, when UTC is 09:51, Hibernate logs value "Fri Mar 29 10:51:56 CET 2019", it's saved as "2019-03-29 09:51:56.519".
• [correct] using Instant, when UTC is 09:38, Hibernate logs value "2019-03-29T09:38:59.172Z", it's saved as "2019-03-29 09:38:59.172".
• [correct] using OffsetDateTime, when UTC is 10:14, Hibernate logs value "2019-03-29T10:14:22.658Z", it's saved as "2019-03-29 10:14:22.658".
• [wrong] using LocalDateTime, when UTC is 16:57, Hibernate logs value "2019-03-28T16:57:35.631", it's saved as "2019-03-28 15:57:35.631".

推荐答案

因此，解决方法似乎是为JDBC连接(而不是JVM)设置UTC时区:

So looks like the fix was to set UTC timezone for the JDBC connection (instead of JVM):

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

，它依靠使用Instant来保持Java端的值，并且使用created_at字段在MySQL和H2中具有DATETIME类型.

and it relies on using Instant for keeping the value on Java side and with created_at field having DATETIME type in MySQL and H2.

产生的缩短的kotlin代码为:

The shortened resulting kotlin code is:

@Entity
data class SomeEntity(
    val createdAt: Instant = Instant.now() // default created date is current UTC time
)

val dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd H:mm:ss")

createdAt = LocalDateTime.parse("2012-11-30 16:13:21", dateTimeFormatter).toInstant(ZoneOffset.UTC)

从乔普·艾根"，"此和这篇文章.

Ideas taken from comments of "Joop Eggen", this and this article.

奖金

我想如果您正在阅读本文，则可能还需要调试SQL查询的帮助.

I guess if you're reading this, you might also need help with debugging SQL queries.

1..要打印在H2上运行的SQL查询，请在连接字符串中添加TRACE_LEVEL_FILE=2和TRACE_LEVEL_SYSTEM_OUT=2(请参见

1. To print SQL queries running on H2 add TRACE_LEVEL_FILE=2 and TRACE_LEVEL_SYSTEM_OUT=2 to connection string (see here):

spring.datasource.url=jdbc:h2:mem:dbname;TRACE_LEVEL_FILE=2;TRACE_LEVEL_SYSTEM_OUT=2;

2..要启用休眠日志:

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=TRACE

3.:要在MySQL中启用查询日志(方法之一，请不要在生产db上使用！):

3. To enable query logs in MySQL (one of the approaches, don't use on production db!):

SET GLOBAL general_log = 'ON';
SET global log_output = 'table';
select * from mysql.general_log ORDER BY event_time DESC;

这篇关于H2中的DATETIME值与从Java/Kotlin插入的MySQL数据库之间不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！