MySQL Connector/J not converting SQL DATE to the time zone of the JVM

Assume the following conditions are met:

Europe/Moscow

+03:00

GMT+14:00

In this case there will be periods during each day when the current date representation (in yyyy-MM-dd format) will be different from Java and database perspectives (database date will be lagging behind).

yyyy-MM-dd

I'm using MySQL Connector/J 8.0 which is time zone aware by default (as opposed to 5.1.46), so it should be sufficient to just set serverTimezone connection property to Europe/Moscow, in case the driver is unable to parse @@time_zone and/or @@system_time_zone.

serverTimezone

Europe/Moscow

@@time_zone

@@system_time_zone

Now, consider the following scenario:

java.sql.Timestamp

java.sql.Date

The date fraction read is expected to be converted back to the time zone of the JVM (this is what I'm observing for Oracle, PostgreSQL and MS SQL Server), i. e. the following test should succeed:

import static java.lang.String.format;
import static java.lang.System.currentTimeMillis;
import static org.assertj.core.api.Assertions.assertThat;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.TimeZone;

import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

public final class TimeZoneTestPartial
private static final TimeZone DEFAULT_TIME_ZONE = TimeZone.getTimeZone("GMT+14:00");

private static final String URL = "jdbc:mysql://localhost:3306/sandbox";

private static final Properties CONNECTION_INFO = new Properties();

static
CONNECTION_INFO.setProperty("user", "...");
CONNECTION_INFO.setProperty("password", "...");
CONNECTION_INFO.setProperty("useSSL", "false");
CONNECTION_INFO.setProperty("serverTimezone", "Europe/Moscow");


@BeforeClass
public static void setUpOnce()
TimeZone.setDefault(DEFAULT_TIME_ZONE);


@Test
@SuppressWarnings("static-method")
public void testDate() throws SQLException
try (final Connection conn = DriverManager.getConnection(URL, CONNECTION_INFO))
try (final Statement stmt = conn.createStatement())
final String tableName = "date_with_time_zone_test";
try
stmt.executeUpdate(format("drop table %s",
tableName));
catch (@SuppressWarnings("unused") final SQLException ignored)
// ignore


stmt.executeUpdate(format("create table %s (value %s not null)",
tableName,
getTimestampType()));

final long clientTimeMillis = currentTimeMillis();

try (final PreparedStatement pstmt = conn.prepareStatement(format("insert into %s (value) values (?)",
tableName)))
pstmt.setTimestamp(1, new Timestamp(clientTimeMillis));
pstmt.executeUpdate();


final String selectSql = format("select * from %s", tableName);
try (final ResultSet rset = stmt.executeQuery(selectSql))
assertThat(rset.next()).isTrue();
final Date date = rset.getDate(1);
assertThat(date).isNotNull();
assertThat(rset.next()).isFalse();

final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
format.setTimeZone(DEFAULT_TIME_ZONE);
assertThat(format.format(date))
.as("date fraction from the database")
.isEqualTo(format.format(new java.util.Date(clientTimeMillis)));


stmt.executeUpdate(format("drop table %s",
tableName));




private static String getTimestampType()
return "datetime"; //"timestamp";




In effect, the test fails for MySQL -- i. e., unlike other mainstream databases, the MySQL driver may return a yesterday date: if I store an SQL TIMESTAMP and read back an SQL DATE, the date fraction will have the time zone of the database, not that of the JVM.

SQL TIMESTAMP

SQL DATE

Am I missing something here?

How do I configure MySQL Connector/J 8.0 so that it behaves consistently with other JDBC drivers?

rset.getDate(1)

DEFAULT_TIME_ZONE

rset.getTimestamp(1)

rset.getDate(1)

1 Answer
1

The SQL TIMESTAMP and DATE types do not contain timezone information.

Similarly, the java.util.Date, java.sql.Date, and java.sql.Timestamp types do not contain timezone information. java.util.Date and java.sql.Timestamp contain the number of milliseconds since Jan. 1 1970 00:00:00 UTC.

Their toString methods make use of the system default timezone, but that does not affect their values.

toString

Since timezone information is not meaningful in Date or Timestamp data, you should not be using it in your comparisons.

Do not compare the values using their String forms. Do not use SimpleDateFormat at all. Instead, compare the actual, meaningful data that each represents, by using a comparison that is not affected by timezones.

The easiest way to do this is by converting the data to the less ambiguous LocalDate and LocalDateTime types:

LocalDateTime localClientTime = new Timestamp(clientTimeMillis).toLocalDateTime();
assertThat(date.toLocalDate())
.as("date fraction from the database")
.isEqualTo(localClientTime.toLocalDate());


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.