Bug #82964 JSR-310 data types created through java.sql types

• Submitted: 13 Sep 2016 5:57
• Modified: 9 Mar 2017 4:59
• Reporter: Philippe Marschall
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 6.0.3
• OS: Any
• Assigned to: Filipe Silva
• CPU Architecture: Any

[13 Sep 2016 5:57] Philippe Marschall

Description:
Connector / J creates JSR-310 data types through (LocalDate, LocalTime and LocalDateTime) by first creating a java.sql instance and and then calling one of the #toXXX conversion methods. In the case of TIMESTAMP on the SQL side and java.sql.Timestamp#toLocalDateTime() on the Java side this is an issue.

The issue arises when you have a TIMESTAMP value in the database that doesn't exist in the local time zone because of a DST offset change. For example 2016-03-27 02:15:00 doesn't exist in Europe/Berlin.
TIMESTAMP does not have a timezone but java.sql.Timestamp has the default time zone of the JVM. If you run your JVM with time zone Europe/Berlin then java.sql.Timestamp will be an hour off and the corresponding LocalDateTime will also be an hour off.

Directly creating a LocalDateTime will solve the issue.

How to repeat:
With the following DDL

CREATE TABLE IF NOT EXISTS date_demo_table (
  id               bigint NOT NULL,
  timestamp_column timestamp(3)
);

Run this test with a JVM in the time zone Europe/Berlin

    try (Connection connection = this.dataSource.getConnection()) {

      BigDecimal id = BigDecimal.ONE;

      try (PreparedStatement insert = connection.prepareStatement(
              "INSERT INTO date_demo_table (id, timestamp_column) VALUES (?, TIMESTAMP '2016-03-27 02:15:00')")) {
        insert.setBigDecimal(1, id);
        insert.executeUpdate();
      }

      try (PreparedStatement select = connection.prepareStatement(
              "SELECT timestamp_column FROM date_demo_table WHERE id = ?")) {
        select.setBigDecimal(1, id);

        int count = 0;
        LocalDateTime selectedTimestamp = null;
        try (ResultSet result = select.executeQuery()) {
          while (result.next()) {
            selectedTimestamp = result.getObject(1, LocalDateTime.class);
            count += 1;
          }
        }
        assertEquals(1, count);
        assertNotNull(selectedTimestamp);
        assertEquals(LocalDateTime.of(2016, 3, 27, 2, 15), selectedTimestamp);
      }
    }

Suggested fix:
Create java.time.LocalDateTime instances directly instead of first creating java.sql.Timestamp instances.

[13 Sep 2016 8:14] Alexander Soklakov

Hi Philippe,

Thanks for the report, we'll take it into account while developing JSR-310 support, that work is still in progress.

[9 Mar 2017 4:59] Daniel So

Posted by developer:
 
Added the following entry to the Connector/J 6.0.6 changelog:

"When Connector/J was reading a TIMESTAMP value into an instance of a JSR-310 data type, the reading was wrong when the time did not exist in the local time zone of the JVM due to a time change for Daylight Saving Time. It was because Connector/J created first a java.sql.Timestamp instance (which used the JVM's time zone) for the value and then converted it to, for example, java.time.LocalDateTime. With this fix, a JSR-310 object is created directly to avoid the conversion. "