4

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".
AbstractVoid
  • 3,583
  • 2
  • 39
  • 39
  • 1
    Compare it with SQL `CURRENT_TIMESTAMP()` (h2+mysql) to see whether the times follow the same clock. – Joop Eggen Mar 28 '19 at 13:22
  • @joop-eggen hm, timestamp differs. On local H2 the CURRENT_TIMESTAMP is `2019-03-28 16:04:29.434` and on both local Docker and AWS RDS MySQL nodes the CURRENT_TIMESTAMP is `2019-03-28 15:04:29`. For the reference, the current local time is `2019-03-28 16:04:29` and current UTC time: `2019-03-28 15:04:29`. I'm not sure if that's the origin of the problem, and if yes, is there a way to fix that? – AbstractVoid Mar 28 '19 at 15:10
  • 1
    So one server runs on UTC, one on local time. I did find https://stackoverflow.com/questions/13176251/set-default-timezone-h2-database keywords: database time zone. BTW the new java time classes have my preference. – Joop Eggen Mar 28 '19 at 15:20
  • @joop-eggen yep, I stumbled across that question multiple times, but setting UTC Timezone for JVM didn't fix the issue. Anyways, that felt like quite a dirty approach. – AbstractVoid Mar 28 '19 at 15:46
  • One would like to be able to use `CURRENT_TIMESTAMP()` almost equal to `now()`, so the databases should be configurable. MySQL maybe. Please should you find something, answer yourself; as that is worthwile. – Joop Eggen Mar 28 '19 at 15:51

1 Answers1

3

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;
AbstractVoid
  • 3,583
  • 2
  • 39
  • 39