When using Jooq, LocalDateTime object saved with unintended timezone in MySql datetime field.

[Kevin Ding]

Dear jOOQ fellow users,

I created a table in MySql with fields of datetime types. On the Java side, the corresponding type is LocalDateTime. The intention was to save the data without worrying about the time zone. For example,  time representation of "2/17/20 00:09:59" for LocalDateTime should be displayed the same after being saving in table. However,   "2020-02-16 16:09:59" was shown in the table after the DB operation. My server system time zone is in GMT and the DB  is in PST.  My jOOQ dependency version is jooq-3.11.5.jar.   My Java version is 9.  Your help is needed and will be  greatly appreciated.

[Lukas Eder]

Hi Kevin,

Time zone issues are a frequently occurring problem when using MySQL, as there are different time zones in play, JVM time zones, server time zones, session time zones, etc. In order to help you best, it would be great if you could show some code that helps reproduce the issue, including the server configuration and your JDBC connection string.

Your time 00:09:59 in GMT corresponds to 16:09:59 in PST. 

From the MySQL manual, please observe the differences between the DATETIME and TIMESTAMP data types:

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

In my opinion, it's quite a mess. Best results are achieved when every system uses the UTC time zone, and user-defined time zones are used only when displaying data to the user.

Thanks,

Lukas