3

Every time a Java LocalDate field is being persisted to a MySQL Date field 1 day is being lost. For instance a Java value of 01 July 2019 would be saved as 30 June 2019 in the Date MySQL column. The MySQL database server is configured to have the 'system time zone' as CDT and I don't have permissions to change this.

The Java program is executed from Europe with the following persistence.xml configuration.

<property name="javax.persistence.jdbc.driver"
            value="com.mysql.cj.jdbc.Driver" />

<property name="javax.persistence.jdbc.url"
            value="...?useTimezone=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=America/Chicago&amp;autoReconnect=true" />

<property name="hibernate.dialect"
            value="org.hibernate.dialect.MySQLInnoDBDialect" />

<property name="hibernate.jdbc.time_zone" value="America/Chicago"/>

The value America/Chicago was chosen because the database server is located in that region (MySQL is configured with the CDT time zone but the CDT abbreviation is not valid in Java)

I have tried without the useTimezone=true&useLegacyDatetimeCode=false and there was no difference. I have tried also with simultaneously having serverTimezone=UTC and hibernate.jdbc.time_zone as UTC and without luck also.

In pom.xml I'm using the following jdbc and hibernate versions.

<mysql-connector.version>8.0.18</mysql-connector.version>
<hibernate.version>5.4.6.Final</hibernate.version>

Using hibernate version 5.4.6 I understand that JPA 2.2 is supported which means that the LocalDate should be supported out of the box without any converter.

I'm not able to get it working. All the things tried were after reading similar issues on StackOverflow. Whatever I try the date will always come -1 day in the database.

Here you can see all the detailsenter image description here

Doru
  • 81
  • 1
  • 10
  • Possible duplicate of [How to stop LocalDate from changing when being saved to a mySQL database](https://stackoverflow.com/questions/46446598/how-to-stop-localdate-from-changing-when-being-saved-to-a-mysql-database) – locus2k Nov 14 '19 at 13:11
  • I'm using hibernate version 5.4.6.Final which supports JPA 2.2 with built-in support for LocalDate which means the LocalDate converter is not needed anymore. – Doru Nov 14 '19 at 13:17
  • Do you get the same problem when you set the client to Central time? The problem resides that while `LocalDate` doesn't store timezone information, mysql applies the timezone offset to the date anyways when persisted. You'll either have to do the conversion before hand or maybe store the date as a `varchar` to make it immutable – locus2k Nov 14 '19 at 13:24
  • Possibly related: https://bugs.mysql.com/bug.php?id=93444 – Gord Thompson Nov 14 '19 at 13:27
  • Yes the client is already on Central time. If you see above the persistence.xml contains in the jdbc url the serverTimezone=America/Chicago and hibernate.jdbc.time_zone value="America/Chicago" both of this setting the client on Central time. So if the client is already the same as the database server then I don't understand why the JDBC driver does this -1 day conversion. – Doru Nov 14 '19 at 13:29
  • @GordThompson - I checked your MySql bug report which is saying this is appearing when the database server and the java client have different settings for the time_zone. For me on MySql the time_zone is CDT so, in order to match on client the same, I have on JDBC persistence.xml the time zone as America/Chicago (on Java the timezone abbreviation CDT is not valid, so I used the more specific America/Chicago). Do you still think this is happening because of the same MySql jdbc driver bug? – Doru Nov 14 '19 at 13:35
  • @Doru - It could still be an issue if CDT really is "Central Daylight Time" which would be UTC-05:00 all year, while "America/Chicago" would be CDT in the summer and CST in the winter. – Gord Thompson Nov 14 '19 at 14:23
  • @GordThompson - I am open to work arounds as long as I can get the date field correctly saved. Are you aware of any workaround which I could use? I would prefer not to change the Date field as a varchar as this is a legacy application and would require more changes. I tried also with simultaneously having serverTimezone=UTC in the jdbc url and hibernate.jdbc.time_zone property as UTC and this did not work too and I really don't understand why this is not working. Do you have any idea why UTC configuration would not work? – Doru Nov 14 '19 at 16:57
  • @Doru - Okay, I just checked a MySQL dev VM; it reports `system_time_zone=MST` for today's date and `system_time_zone=MDT` for a date back in the summer. So, your server may well be switching back and forth between CDT and CST just like "America/Chicago" (assuming `time_zone=SYSTEM`). Perhaps you could try launching your application with `-Duser.timezone=America/Chicago` to see if that works any better than the Hibernate config tweak(s). – Gord Thompson Nov 14 '19 at 17:54
  • @GordThompson - Your last suggestion worked. After I added -Duser.timezone=America/Chicago in my JUnit VM Arguments doubled by the serverTimezone=America/Chicago and hibernate.jdbc.time_zone as America/Chicago in persistence.xml the LocalDate field started to be saved correctly. The difficulty is that this program is going to execute in AWS Java Lambda where the JVM timezone is predefined by the Java lambda environment and cannot be changed (cannot pass -D arguments in AWS Java Lambda env). – Doru Nov 14 '19 at 21:55
  • It seems there are two different bugs generating this issue the 1st bug is the one which you mentioned https://bugs.mysql.com/bug.php?id=93444 and the 2nd bug is in the Connector/J MySQL jdbc driver which is ignoring the serverTimezone and/or hibernate.jdbc.time_zone configuration I have added in persistence.xml (2nd bug could be a Hibernate bug also). – Doru Nov 14 '19 at 22:00

0 Answers0