Ok, after having stared at things for a while, I believe that the
problem is that Rene's backend (computer?) is not recognizing local
summer time (daylight savings).
When a timestamp is inserted into a table, the time is changed to UTC.
The amount of the time shift is determined by whether or not the date to
be inserted is in normal time or local summer (daylight savings). If no
timezone is specified along with the timestamp, the current timezone is
determined by various means (TZ, PGTZ, or SQL set time zone).
The dates of test cases 1 and 4 do not fall in daylight savings time so
there is no issue there. For cases 2, 3, 5, and 6, the date is June 2,
1970 so daylight savings time is in effect (Rene's zone is now CEST [+2]
instead of CET [+1]). Now, for cases 2 and 3, the time is being shifted
by the backend but the shift is one hour instead of two! So the date
returned by the backend, while correct given the one hour shift, is not
correct when changed back to UTC during comparisons. It seems that
postgresql is not realizing that the timezone that Rene is in observes
daylight savings time. Those two cases passed for me in Toronto because
postgresql knows that EST observes daylight savings time by becoming
EDT.
Rene, CET becomes CEST in summer, but does your locale actually observe
it? (Like Saskatchewan, Canada, which is in Canada/Central but doesn't
observe daylight savings).
Why do tests 5 and 6 not fail? The setTimestamp method of
PreparedStatement uses Java's internal date/time processing
functionality to shift the date to UTC before sending to the backend.
Java does know about CET and CEST so the shift is performed correctly.
When the timestamps are retrieved from the database, they are retrieved
in CET, but 1970-06-02 07:13:00+01 (what the backend returns) is
the same as 1970-06-02 08:13:00+02 so the tests pass. For these tests,
the backend should actually be returning 1970-06-02 08:13:00+02.
The JDBC interface is fine (on the assumption that Java does correct
shifting).. The problem is with the backend and/or Rene's computer (or
some wackyness in timezone observances).
Liam
On Sun, Nov 04, 2001 at 06:44:22PM +0100, Rene Pijlman wrote:
> 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>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com
