We have migrated our application from MySQL 5.1.6 to MySQL 8.0.16
While retrieving timestamp column from MySQL 8 server using mysql-connector-java jdbc driver (version 8.0.18, 8.0.19), we are getting incorrect value for the date value which is in daylight saving time (DST).
Both our server hosting java application & MySQL database are in EST.
Here, we have a column created_date having type datetime. It has value '2020-03-17 23:01:54' for a specific row. While retrieving same data through JDBC as:
Timestamp timestamp = resultSet.getTimestamp("created_date");
we are getting value as 2020-03-18 00:01:54.0 i.e. DST is getting applied.
There is a known bug in MySQL: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-known-issues-limitations.html
It suggest to use add additional parameter in jdbc url serverTimezone=UTC. But we can't use UTC because we want to use deployment specific timezone in our application/database.
Note: We were not facing this issue with MySQL 5.1.
Additional update: While inserting data into database, If I use jdbc url with parameter serverTimezone=UTC, then while retrieving timestamp column I get correct value.
So it seems with serverTimezone=UTC, JDBC driver is not applying DST while storing timestamp in database. So workaround suggested (i.e. serverTimezone=UTC) seems to be working.
But If I insert data using Load Data Local file command using JDBC url containing serverTimezone=UTC i.e.
PreparedStatement stmt = connection.prepareStatement("LOAD DATA LOCAL INFILE.....")
it seems serverTimezone=UTC flag is getting ignored & data is getting stored with DST applied timestamp value. So while retrieving timestamp, we are getting incorrect value.
Appreciate any suggestions.
