I wrote the ticket https://jira.mariadb.org/browse/CONJ-109 a couple of years ago. Back then my opinion was that MariaDB JDBC connector (1.1.7) handled timestamp in a fragile way.
Since TIMESTAMP column data is transferred between JDBC and the server in plain text for example “2017-02-15 10:32:15” care must be taken in what time zone/daylight savings time this String is interpreted. My opinion then (and now) is that the JDBC driver should handle such details for the developer for all data types that are time zone aware on the server. The data type TIMESTAMP is timezone aware since it internally always is stored in UTC in the SQL server. Therefore, my opinion, is that a developer never should bother with specifying timezone information. The methods ResultSet.getTimestamp(int) and PreparedStatement.setTimestamp(int parameterIndex, Timestamp x) returns and accept (respectively) a Java object that is timezone aware (java.sql.Timestamp/ java.util.Date that both internally stores times as UTC). Because of this, both sides are time zone aware and (again my opinion) the developer should not be bothered with time zone details. If the developer want a present a Date-object (to the end user) from a ResulSet in a different timezone than UTC he is free to use a DateFormater/Calender object like normal.
I had some discussions with developers at MariaDB regarding ticket
CONJ-109 and finally it was rejected. I found a suitable workaround: By adding
as an URL parameter both the JDBC connector and the server both interpret the date String in UTC timezone and the developer does not need take any special time zone care when using ResultSet/ PreparedStatement.
Since that time (version 1.1.7) much has happened in the JDBC connector. In version 1.5.7 my workaround no longer work. The JDBC code that parses/formats timetstamp in both SELECTs and INSERTs have been re-written. More specifically: the JDBC serverTimezone parameter is now effectively ignored in the parsing methods (unless another parameter also is specified).
By studying the source code I have found a workaround to add to my previous workaround:
By adding useLegacyDatetimeCode=false the JDBC once again respects the serverTimezone parameter.
If you don’t want to make any drastic changes to the time zone parsing code or once again change what JDBC parameter that exist I hope that you can perform these tasks:
- Properly document the useLegacyDatetimeCode parameter on https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/. Currently it is only (semi-hidden) documented on https://mariadb.com/kb/en/mariadb/mariadb-connector-j-130-release-notes/. I feel it is important that the documentation should clearly state that there is a dependency between useLegacyDatetimeCode=false and serverTimezone (that serverTimezone cannot be used unless useLegacyDatetimeCode is set to false).
- Add a JUnit test case with this combination of parameters that will break if you in the future changes this (so that the code I work in will not break with future updates of JDBC). Feel free to use my test case I have written (to be inserted into https://github.com/MariaDB/mariadb-connector-j/blob/master/src/test/java/org/mariadb/jdbc/DateTest.java)