Funny timezone shift causes failure in test suite - Mailing list pgsql-jdbc

The JDBC driver's test suite with current CVS still has one
failure in the TimestampTest. This is with Liam's fixes of a
couple of weeks ago already applied.

I did some debugging and tracing and I have a hard time
explaining what's going on. Perhaps someone can help me out
here.

Below is a detailed transcript of what's happening in the
relevant parts of testGetTimestamp() and testSetTimestamp().
Both client and server were running in the CET (+1:00) timezone.

Test cases 1-3 construct a SQL string with a hard coded date
without a timezone indication, so conversion from localtime to
UTC is done by the backend. Test cases 4-6 go through
Statement.setTimestamp() which converts to UTC in the driver.

The funny thing is that test cases 1 and 2/3 use the same code,
while 1 succeeds and 2 and 3 fail. The only difference appears
to be the actual date used in the test. The explanation may be
in test cases 5 and 6, which succeed with the same dates but
with different code. For some reason, the 1970 date gets a 2
hour time shift from CET (+1) to UTC, while the 1950 date gets a
1 hour time shift as I expected.

So it appears that the time shift algorithm in the backend
differs from the time shift algorithm used in setTimestamp() in
the driver. The driver gives the 1970 date a different time
shift than the 1950 date, whereas the backend treats them both
the same.

This is the mapping table:

         Timestamp in CET (+1)     In UTC

Backend  1950-02-07 15:00:00       1950-02-07 14:00:00.0
         1970-06-02 07:13:00       1970-06-02 06:13:00.0
                    ^^

Driver   1950-02-07 15:00:00.0     1950-02-07 14:00:00.0
         1970-06-02 08:13:00.0     1970-06-02 06:13:00.0
                    ^^

Does anyone understand why this is happening and which of the
two algorithms is correct?


Test case 1: passes
-------------------
testGetTimestamp():
stmt.executeUpdate(JDBC2Tests.insertSQL("testtimestamp","'1950-02-07
15:00:00'"))
Sends to the backend: INSERT INTO testtimestamp VALUES
('1950-02-07 15:00:00')
Backend returns: 1950-02-07 15:00:00+01
Matches: getTimestamp(1950, 2, 7, 15, 0, 0, 0)

Test case 2: fails
------------------
testGetTimestamp():
stmt.executeUpdate(JDBC2Tests.insertSQL("testtimestamp",
"'"+getTimestamp(1970, 6, 2, 8, 13, 0, 0).toString() + "'"))
Sends to the backend: INSERT INTO testtimestamp VALUES
('1970-06-02 08:13:00.0')
Backend returns: 1970-06-02 08:13:00+01
Does not match: getTimestamp(1970, 6, 2, 8, 13, 0, 0)

Test case 3: passes
-------------------
testGetTimestamp():
stmt.executeUpdate(JDBC2Tests.insertSQL("testtimestamp","'1970-06-02
08:13:00'"))            Sends to the backend: INSERT
INTO testtimestamp VALUES ('1970-06-02 08:13:00')
Backend returns: 1970-06-02 08:13:00+01
Does not match: getTimestamp(1970, 6, 2, 8, 13, 0, 0)

Test case 4: passes
-------------------
pstmt.setTimestamp(1, getTimestamp(1950, 2, 7, 15, 0, 0, 0));
Sends to the backend: INSERT INTO testtimestamp VALUES
('1950-02-07 14:00:00.0+00')
Backend returns: 1950-02-07 15:00:00+01
Matches: getTimestamp(1950, 2, 7, 15, 0, 0, 0)

Test case 5: passes
-------------------
pstmt.setTimestamp(1, getTimestamp(1970, 6, 2, 8, 13, 0, 0));
Sends to the backend: INSERT INTO testtimestamp VALUES
('1970-06-02 06:13:00.0+00')
Backend returns: 1970-06-02 07:13:00+01
Matches: getTimestamp(1970, 6, 2, 8, 13, 0, 0)

Test case 6: passes
-------------------
pstmt.setTimestamp(1, getTimestamp(1970, 6, 2, 8, 13, 0, 0));
Sends to the backend: INSERT INTO testtimestamp VALUES
('1970-06-02 06:13:00.0+00')
Backend returns: 1970-06-02 07:13:00+01
Matches: getTimestamp(1970, 6, 2, 8, 13, 0, 0)

Regards,
René Pijlman <rene@lab.applinet.nl>