Mismatch between DATETIME values in H2 and MySQL d

问题:

TLDR:

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

Full context:

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

• 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.

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.

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.

Here are shortened snippets of kotlin code that I have.

Code that works for H2 but doesn't work for MySQL in Docker and AWS:

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

Code that works for MySQL in Docker and AWS but doesn't work for 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'

This runs on: 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.

Update

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.

Saving to local H2:

• [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".

Saving to MySQL in local docker:

• [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".

回答1:

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

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.

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.

Bonus

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

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. To enable hibernate logs:

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. 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;