UTC offset subtracted from TIMESTAMP WITH TIME ZONE value in HSQLDB

Question

I am using HSQLDB 2.4.0 and seeing unexpected TIMESTAMP WITH TIME ZONE values being returned.

I am running the following code with a JVM time zone of UTC+2

try (Connection connection = this.dataSource.getConnection();
     PreparedStatement preparedStatement = connection.prepareStatement(
             "SELECT '1960-01-01 23:03:20+02:00' "
           + "FROM (VALUES(0))");
     ResultSet resultSet = preparedStatement.executeQuery()) {

  OffsetDateTime expected = OffsetDateTime.parse("1960-01-01T23:03:20+02:00");
  while (resultSet.next()) {
    assertEquals(expected, resultSet.getObject(1, OffsetDateTime.class));
    assertEquals("1960-01-01 23:03:20+02:00", resultSet.getObject(1, String.class));
  }
}

The second assertion succeeds but the first fails. Instead of the expected 1960-01-01T23:03:20+02:00 value I get 1960-01-01T21:03:20+02:00 which is two hours before the expected value. Is this a bug or the expected behaviour?

edit

After some more investigation it seems that not the UTC offset of the local time zone but the UTC offset of the TIMESTAMP WITH TIME ZONE gets subtracted.

If I change '1960-01-01 23:03:20+02:00' to CAST('1960-01-01 23:03:20+02:00' AS TIMESTAMP WITH TIME ZONE) + INTERVAL '2:00' HOUR TO MINUTE I get 1960-01-01T21:03:20+02:00. Similarly '1960-01-01 23:03:20+05:00' is five hours off but CAST('1960-01-01 23:03:20+02:00' AS TIMESTAMP WITH TIME ZONE) + INTERVAL '5:00' HOUR TO MINUTE gives me 1960-01-01T21:03:20+05:00.

Answer 1

This looks like a bug that has been fixed for the next release. You can checkout the SVN /base/trunk code and compile with Gradle or Ant.