java - H2 中的 DATETIME 值与从 Java/Kotlin 插入的 MySQL 数据库之间的不匹配

Question

TLDR:

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

完整上下文:

我在数据库中有一个包含 DATETIME 字段的表，我想在以下位置插入行:

• 默认情况下(当没有给出值时)将存储当前 UTC 时间
• 或者如果给出了 UTC 日期时间，则应该在没有额外的时区转换。

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

而且我很难在所有 3 个实例中正确保存日期时间。

到目前为止，要么是本地和 aws mysql 实例获取正确的值，但本地 H2 获取错误的值，要么是其他方式，当本地 H2 获取正确的值但 MySQL 实例获取错误的值时。

这是我拥有的 kotlin 代码的简短片段。

适用于 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

我在网上和 stackoverflow 上看到了很多问题，但不幸的是，没有一个解决方案可以解决我的问题。

更新

在使用多种方法进行额外调试后，查看 Hibernate、H2 和 MySQL 的日志，看起来 H2 和 MySQL 的 UTC 时间处理方式完全相反。

保存到本地 H2:

• [错误] 使用日期，当 UTC 为 09:55 时，Hibernate 记录值“Fri Mar 29 10:55:09 CET 2019”，保存为“2019-03-29 10:55:09.412”。
• [错误] 使用 Instant，当 UTC 为 16:48 时，Hibernate 记录值“2019-03-28T16:48: 18.270Z”，保存为“2019-03-28 17:48:18.27”。
• [错误] 使用 OffsetDateTime，当 UTC 为 10:11 时，Hibernate 记录值“2019-03-29T10:11: 30.672Z”，保存为“2019-03-29 11:11:30.672”。
• [正确] 使用 LocalDateTime，当 UTC 为 16:50 时，Hibernate 记录值“2019-03-28T16:50: 20.697”，保存为“2019-03-28 16:50:20.697”。

在本地 docker 中保存到 MySQL:

• [正确] 使用日期，当 UTC 为 09:51 时，Hibernate 记录值“Fri Mar 29 10:51:56 CET 2019”，保存为“2019-03-29 09:51:56.519”。
• [正确] 使用 Instant，当 UTC 为 09:38 时，Hibernate 记录值“2019-03-29T09:38: 59.172Z”，保存为“2019-03-29 09:38:59.172”。
• [正确] 使用 OffsetDateTime，当 UTC 为 10:14 时，Hibernate 记录值“2019-03-29T10:14: 22.658Z”，保存为“2019-03-29 10:14:22.658”。
• [错误] 使用 LocalDateTime，当 UTC 为 16:57 时，Hibernate 记录值“2019-03-28T16:57: 35.631”，保存为“2019-03-28 15:57:35.631”。

Answer 1

看起来修复是为 JDBC 连接(而不是 JVM)设置 UTC 时区:

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

它依赖于使用 Instant 将值保存在 Java 端，并且 created_at 字段在 MySQL 和 H2 中具有 DATETIME 类型。

缩短后的 kotlin 代码是:

@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)

想法取自“Joop Eggen”的评论，this和 this文章。

奖金

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

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

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

2.启用 hibernate 日志:

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 中启用查询日志(方法之一，不要在生产数据库上使用!):

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