#1729 PreparedStatement.setTimestamp ignores Calendar timezone

[Pijus Krisiukenas]

The code below executes and retrieves the result of a query that selects a TIMESTAMP parameter in a JVM with a non-UTC timezone. The parameter is set and the query result retrieved explicitly with the UTC timezone. At the end an exception is thrown if the parameter value and the returned value is not the same.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.TimeZone;

public class HSQLDBDateParameterTest {
  public static void main(String[] args) throws Exception {
    TimeZone.setDefault(TimeZone.getTimeZone("Europe/Athens"));

    Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "SA", "");
    Statement statement = connection.createStatement();
    statement.execute("create table table1 (int int)");
    statement.execute("insert into table1 values (1)");

    PreparedStatement preparedStatement = connection.prepareStatement(
        "SELECT ? FROM table1"
    );
    Timestamp expectedDate = new Timestamp(1000);
    preparedStatement.setTimestamp(1,expectedDate, Calendar.getInstance(TimeZone.getTimeZone("UTC")));

    ResultSet rs = preparedStatement.executeQuery();
    rs.next();
    Timestamp retrievedTimestamp = rs.getTimestamp(1, Calendar.getInstance(TimeZone.getTimeZone("UTC")));

    if (!expectedDate.equals(retrievedTimestamp)) throw new Exception(String.format("%s not equal to %s", expectedDate, retrievedTimestamp));
  }
}

The expectation is that the parameter value will be the same as the query result and the method will exit without an exception. Instead the exception is thrown:

Exception in thread "main" java.lang.Exception: 1970-01-01 02:00:01.0 not equal to 1970-01-01 04:00:01.0
    at HSQLDBDateParameterTest.main(HSQLDBDateParameterTest.java:29)

The exception is not thrown if the JVM timezone is set to UTC. It appears that PreparedStatement.setTimestamp() ignores the provided timezone and uses the default timezone.

[Fred Toussi]

This is probably working fine.

The "SELECT ? FROM table1" has a parameter with undefined data type, which is defaulted to CHARACTER. After that, you cannot expect retrieved values to match inserted values, as different conversions to / from character is performed.

If you want to explore this further, try this "SELECT CAST(? AS TIMESTAMP WITH TIME ZONE) FROM table1" instead.

[Bas de Bakker]

Why should the conversions to/from character be different? The same timezone is explicitly specified in both setTimestamp and getTimestamp. Shouldn't those conversions to/from character be using those timezones?

(Also, it works as expected in PostgreSQL, SQL Server, Oracle, SAP HANA, MariaDB and MySQL.)

[Fred Toussi]

Ideally, conversions should be symmetrical. I will include this issue in a future review of JDBC compatibility.

[Fred Toussi]

Fixed and committed to SVN for the next release.