Store and retrieve a date in MySQL without any timezone information using MySQL Connector/J 8.0

Question

We are now in the process of updating the MySQL Connector/J of a Spring Boot application from version 5 to 8 (we are actually updating the Spring Boot version from 2.0 to 2.1, but I don't think it's relevant for our problem).

After upgrading the connector (the database remains the same: MySQL 5.7) we found that all the DATETIME values stored in the database were being shifted by the timezone difference between the server and the client (UTC and UTC+1). If we, for example, try to read a value like 2019-01-01 02:17:00 we are getting 2019-01-01 03:17:00.

We are aware that the new connector is the one making that time shift but we need our dates to be timezone independent. According to the documentation of MySQL 5.7 it looks like DATETIME is the way to go:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

The only thing we can do right now is either downgrade the connector to previous version or change all the DATETIME columns to a BIGINT.

Is there any way of storing a date and a time without any timezone automatic conversion in MySQL?

i think this one [Should MySQL have its timezone set to UTC?](https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc/19075291) answers all your questions or doubts.. — Raymond Nijland, Sep 02 '19 at 11:29
Our problem is that the Java connector is always doing the conversion even for datetime values which do not get converted by MySQL. — Jago, Sep 02 '19 at 11:59
@Jago - As suggested in the [documentation you cited](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-other-changes.html) can you add the JVM default time zone (`TimeZone.getDefault().getID()`) as a connection string parameter, e.g., `serverTimezone=Europe/Paris` ? — Gord Thompson, Sep 02 '19 at 12:42
@GordThompson That works, we tried fixing it to UTC but then two clients on different timezones would get different dates. This way you are basically tricking the connector into thinking that que MySQL Server is in the same timezone as you, hence skipping the conversion. Thanks! — Jago, Sep 02 '19 at 13:55

score 2 · Answer 1 · answered Sep 02 '19 at 12:10

2

Adding timezone to connection string should solve this problem. Try to add following text to your connection url:

useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=CET

answered Sep 02 '19 at 12:10

arifng

726
1
12
22

`useJDBCCompliantTimezoneShift ` and `useLegacyDatetimeCode` have been removed from Connector/J 8 so they do nothing, and fixing the timezone to `CET` will make any client on other timezones to get a wrong date. – Jago Sep 02 '19 at 13:58
CET is just an example, it should be system time zone – arifng Sep 02 '19 at 14:04
If you want to set timezone per client, then you have to set session time zone. Please check this topic: https://dba.stackexchange.com/questions/223611/mysql-connection-pool-set-timezone – arifng Sep 02 '19 at 14:09

score 1 · Accepted Answer · answered Sep 02 '19 at 14:47

In the past I've used this trick to make sure that the effective server time zone is the same as the client time zone:

String connectionUrl = "jdbc:mysql://localhost:3307/mydb?useUnicode=true"
            + "&serverTimezone=" + ZoneId.systemDefault().getId();
System.out.println(connectionUrl);
// jdbc:mysql://localhost:3307/mydb?useUnicode=true&serverTimezone=America/Denver

Connection conn = DriverManager.getConnection(connectionUrl, myUid, myPwd);

Store and retrieve a date in MySQL without any timezone information using MySQL Connector/J 8.0

2 Answers2