[#CONJ-433] Clarify the JDBC parameters serverTimezone and useLegacyDatetimeCode with respect to session time zone

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.

More specifically:
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

&serverTimezone=UTC&sessionVariables=time_zone='+00:00'

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.

Problem:

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).

Workaround:

By studying the source code I have found a workaround to add to my previous workaround:

&serverTimezone=UTC&useLegacyDatetimeCode=false&sessionVariables=time_zone='+00:00'

By adding useLegacyDatetimeCode=false the JDBC once again respects the serverTimezone parameter.

To do for MariaDB:

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:

/**

     * Tests that formatting and parsing of TIMESTAMP sent as text Strings between JDBC and server

     * is handled correctly when JDBC and server agrees to communicate in a specific time zone in

     * the session/connection, different from both what JVM and server is running.

*/

    @Test

    public void testClientServerSessionTimeZone() throws SQLException, ParseException {

        createTable("connection_session_timestamp", "name varchar(40), birth_ts timestamp");

        TimeZone utcTimeZone = TimeZone.getTimeZone("utc");

        SimpleDateFormat utcDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        utcDateFormat.setTimeZone(utcTimeZone);

        java.util.Date aliceBirthDate = utcDateFormat.parse("2017-11-24 10:32:00");

        java.util.Date bobBirthDate   = utcDateFormat.parse("2016-12-25 16:44:00");

        Connection gmtMinus2Connection = null;

        Connection utCconnection = null;

        try {

            //Create a baseline connection object that uses UTC time zone. This connection is used

            //to manually parse/format UTC time Strings without interference of JDBC

            utCconnection = setConnection("&sessionVariables=time_zone='+00:00'");

            //Create a row in the database by manually formatting

            //the TIMESTAMP in UTC so we are sure the server has it stored correctly

            String sql = "insert into connection_session_timestamp (name, birth_ts) VALUES('Alice', '" +

                            utcDateFormat.format(aliceBirthDate) + "')";

            utCconnection.createStatement().executeUpdate(sql);

            String parameters = "";

            //GMT -2 hours in the Atlantic Ocean is very sparsely populated so it is unlikely that

            //a developer/integration server for MariaDB has this time zone set for either JUnit JVM

            //or MariaDB server. This makes it very likely that JUnit JVM and MariaDB server is run

            //in a different time zone than session time zone. This makes it more likely for this

            //test case to catch problems.

            //This parameter makes the server talk in the specified /time zone.

            parameters += "&sessionVariables=time_zone='-02:00'";

            //Note: Because of a "feature" it is not correct to specify "Etc/GMT-2" for a Java

            //Timezone when you mean GMT -2, instead a real place is used for clarity. For more info

            //see https://bugs.openjdk.java.net/browse/JDK-4813746 or

            //https://github.com/moment/moment-timezone/issues/167

            //This parameter makes the JDBC client code talk in the specified time zone (must be the

            //same as 'time_zone' above.

            parameters += "&serverTimezone=Atlantic/South_Georgia";

            //Without this parameter the serverTimezone does not take effect. This test case may

            //(should?) fail if it is changed.

            parameters += "&useLegacyDatetimeCode=false";

            gmtMinus2Connection = setConnection(parameters);

            //Test PreparedStatement.setTimestamp() (without Calendar parameter)

            PreparedStatement preparedStatement = gmtMinus2Connection.prepareStatement(

                "insert into connection_session_timestamp (name, birth_ts) VALUES(?, ?)");

            preparedStatement.setString(1, "Bob");

            preparedStatement.setTimestamp(2, new Timestamp(bobBirthDate.getTime()));

            preparedStatement.executeUpdate();

            //Test ResultSet.getTimestamp() method (without Calendar parameter)

            ResultSet resultSet = gmtMinus2Connection.createStatement()

                            .executeQuery("select * from connection_session_timestamp");

            resultSet.next();

            assertEquals("Alice", resultSet.getString(1));

            assertEquals(aliceBirthDate, new java.util.Date(resultSet.getTimestamp(2).getTime()));

            resultSet.next();

            assertEquals("Bob", resultSet.getString(1));

            assertEquals(bobBirthDate, new java.util.Date(resultSet.getTimestamp(2).getTime()));

            //Verify that values has been correctly inserted into the database using UTC and

            //manual String parsing of timestamp

            resultSet = utCconnection.createStatement()

                            .executeQuery("select * from connection_session_timestamp");

            resultSet.next();

            assertEquals("Alice", resultSet.getString(1));

            assertEquals(utcDateFormat.format(aliceBirthDate),

                resultSet.getString(2).replaceAll("\\.0", ""));

            resultSet.next();

            assertEquals("Bob", resultSet.getString(1));

            assertEquals(utcDateFormat.format(bobBirthDate),

                resultSet.getString(2).replaceAll("\\.0", ""));

        } finally {

            gmtMinus2Connection.close();

            utCconnection.close();

[CONJ-433] Clarify the JDBC parameters serverTimezone and useLegacyDatetimeCode with respect to session time zone Created: 2017-02-16 Updated: 2020-06-29
Status:	Open
Project:	MariaDB Connector/J
Component/s:	documentation
Affects Version/s:	1.5.7
Fix Version/s:	None