MySQL client vs server time zone

Question

I'm running into an issue where MySQL stores different date time values than the client passes. The server runs in UTC and the client in a different time zone. Somehow MySQL seems to convert date time values between the client and server time zone even though the SQL types DATE, TIME and TIMESTAMP all have no time zone. No other database I tested so far has this behaviour.

The following code can be used to reproduce the issue. When the server runs in UTC the code only works when the client also runs in UTC.

try (Connection connection = this.dataSource.getConnection();
     PreparedStatement preparedStatement = connection.prepareStatement(
             "SELECT ? = DATE '1988-12-25', ? = TIME '15:09:02', ? = TIMESTAMP '1980-01-01 23:03:20'")) {
  preparedStatement.setDate(1, java.sql.Date.valueOf("1988-12-25"));
  preparedStatement.setTime(2, java.sql.Time.valueOf("15:09:02"));
  preparedStatement.setTimestamp(3, java.sql.Timestamp.valueOf("1980-01-01 23:03:20"));
  try (ResultSet resultSet = preparedStatement.executeQuery()) {
    while (resultSet.next()) {
      System.out.println(resultSet.getBoolean(1));
      System.out.println(resultSet.getBoolean(2));
      System.out.println(resultSet.getBoolean(3));
    }
  }

}

I'm using

• MySQL 5.7.14
• mysql-connector-java 6.0.5
• Oracle Java 1.8.0_131

My JDBC URL is just jdbc:mysql://host:port/database

edit

My reasoning why time zones should not play a role here and no time zone conversion should happen is two fold. Firstly on the SQL level TIMESTAMP is an alias for TIMESTAMP WITHOUT TIME ZONE which strongly implies that unlike TIMESTAMP WITH TIME ZONE it values have no time zone. In other words values are not instants in time but rather in local date time values.

Secondly that java.sql.Timestamp is in JVM time zone is merely artefact of being a subclass of java.util.Date. (I am aware that java.util.Date has no time zone). The Javadoc of java.sql.Timestamp of makes it quite clear that relationship is only for implementation purposes.

I feel both of these assertions are confirmed by the fact that in Java SE 8 / JDBC 4.2 java.sql.Timestamp is mapped to java.time.LocalDateTime and not java.time.ZonedDateTime or java.time.OffsetDateTime.

edit 2

I do not understand why TIMESTAMP values are subject to time zone conversion. Unlike TIMESTAMP WITH TIME ZOONE These are "local" values and do not have an associated time zone and should therefore have no time zone conversion applied to them.

Answer 1

I agree with the comment that timezone handling under MySQL JDBC can be confusing, but in this case ...

MySQL stores different date time values than the client passes

... is not quite correct. It interprets and/or displays the string representation of the same datetime value in the context of the server timezone.

First you need to understand that java.sql.Timestamp#valueOf creates a Timestamp value in the local timezone under which the Java Virtual Machine is running. So, for my machine which is on "Mountain Time" in Canada (UTC-7 for Standard Time):

System.out.printf("Local (client) timezone is %s%n", TimeZone.getDefault().getID());

java.sql.Timestamp tStamp = java.sql.Timestamp.valueOf("1980-01-01 23:03:20");

SimpleDateFormat sdfLocal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
SimpleDateFormat sdfUTC = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
sdfUTC.setCalendar(Calendar.getInstance(TimeZone.getTimeZone("UTC")));

System.out.println("tStamp = java.sql.Timestamp.valueOf(\"1980-01-01 23:03:20\")");
System.out.printf("                        ... which is %s%n", sdfLocal.format(tStamp));
System.out.printf("                        ... which is %s%n", sdfUTC.format(tStamp));

prints

Local (client) timezone is America/Edmonton
tStamp = java.sql.Timestamp.valueOf("1980-01-01 23:03:20")
                        ... which is 1980-01-01 23:03:20 MST
                        ... which is 1980-01-02 06:03:20 UTC

Now, when we pass that Timestamp value to a PreparedStatement and send it off to a MySQL server that is using the UTC timezone, the server interprets and displays string literal representations of that MySQL TIMESTAMP as UTC:

String connUrl = "jdbc:mysql://localhost/mydb";
try (Connection conn = DriverManager.getConnection(connUrl, myUid, myPwd)) {
    String sql = "SELECT CAST((TIMESTAMP ?) AS CHAR) AS foo";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setTimestamp(1, tStamp);
        try (ResultSet rs = ps.executeQuery()) {
            rs.next();
            System.out.printf("String representation of TIMESTAMP value at server: %s%n", 
                    rs.getString(1));
        }
    }
}

producing

String representation of TIMESTAMP value at server: 1980-01-02 06:03:20

If the MySQL server had been running under Toronto time (UTC-5 for Standard Time) the output would have been ...

String representation of TIMESTAMP value at server: 1980-01-02 01:03:20

... not because the MySQL TIMESTAMP value is different, but because the string representation of that value in the context of the MySQL server timezone is different.

Answer 2

I guess it is like Gord Thompson and spencer7593 stated, that the timezone of the server and that of the connection-string of the client differ and a conversion between timezones has to be made.

I also learned something while reading your edits, as in that the java.sql.Timestamp contains no timezone.

As spencer7593 stated: "The server is interpreting the literal in the context of the server timezone".

My two approaches here would be:

1. Connect to server (UTC) with your timezone in the connection-string(e.g. &serverTimezone=Europe/Zurich).

2. Alternatively pass an instance of an Calendar with your local timezone (Calendar.getInstance()) to PreparedStatement.setTimestamp.