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?
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);