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>

Sun, 04 Nov 2001 18:44:22 +0100
Rene Pijlman <rene@lab.applinet.nl>
Funny timezone shift causes failure in test suite
On Sun, 04 Nov 2001 18:44:22 +0100, I wrote:
>Test case 3: passes

Oops, test case 3 failed as well.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Sun, 04 Nov 2001 19:54:17 +0100
Rene Pijlman <rene@lab.applinet.nl>
Re: Funny timezone shift causes failure in test suite

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

Mon, 5 Nov 2001 18:26:28 -0500
Liam Stewart <liams@redhat.com>
Re: Funny timezone shift causes failure in test suite
On Tue, Nov 06, 2001 at 05:27:44AM +0000, Thomas Lockhart wrote:
> I glanced at the CET timezone database on my Linux box and it seems that
> there is a DST gap between 1944 and 1977. Certainly I see entries in,
> for example, PST8PDT for this time period whereas for CET I see no
> entries at all.

Europe/Amsterdam has a nice big gap between 1945 and 1977. Perhaps they
stopped observing it for a while and Java's not that bright?

> Is Java guaranteed to use the system timezone database? In any case,
> istm that there is a discrepency between Rene's expectations of DST
> behavior and the info in the zoneinfo database.

I haven't looked the internals of any JRE, but I would think that the
system's timezone database would not even be acknowledged. Java most
likely has its own internal database.

Liam

-- 
Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com

Tue, 6 Nov 2001 12:22:38 -0500
Liam Stewart <liams@redhat.com>
Re: Funny timezone shift causes failure in test suite
On Mon, 5 Nov 2001 18:26:28 -0500, you wrote:
>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).

Its running Red Hat Linux 7.1. Is that buggy? ;-)

linuxconf says zone is "Europe/Amsterdam" and I remember
selecting that when I installed it. date +%Z says "CET".

This is /etc/sysconfig/clock:
ZONE="Europe/Amsterdam"
UTC=false
ARC=false

By the way, according to a reliable local source there was no
summer time in the Netherlands between 1945 and 1977, but I'm
not sure if the timezone configs of Red Hat are aware of that
:-)

>Rene, CET becomes CEST in summer, but does your locale actually observe
>it? 

Euh... how can I tell?

Thanks for your efforts.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Wed, 07 Nov 2001 23:01:13 +0100
Rene Pijlman <rene@lab.applinet.nl>
Re: Funny timezone shift causes failure in test suite
On Wed, Nov 07, 2001 at 11:01:13PM +0100, Rene Pijlman wrote:
> On Mon, 5 Nov 2001 18:26:28 -0500, you wrote:
> >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).
> 
> Its running Red Hat Linux 7.1. Is that buggy? ;-)
> 
> linuxconf says zone is "Europe/Amsterdam" and I remember
> selecting that when I installed it. date +%Z says "CET".
> 
> This is /etc/sysconfig/clock:
> ZONE="Europe/Amsterdam"
> UTC=false
> ARC=false
> 
> By the way, according to a reliable local source there was no
> summer time in the Netherlands between 1945 and 1977, but I'm
> not sure if the timezone configs of Red Hat are aware of that
> :-)

That's it. (At least) Sun's JRE seems to be braindead when it comes to
timezones..  I made a small test program that runs through a sequence of
years and for each year, it takes a date in the winter and a date in the
summer and checks whether or not the calendar is in daylight savings
time. From 1945 to 1977, it reports that in the summer, daylight savings
time is observed, which it shouldn't be. 

After poking around some, I think that Java isn't concerned with
historical behaviour (ICU [Internation Components for Unicode] isn't
AFAICS, and the ICU Java classes developped by Taligent were integrated
into Sun's JDK 1.1...)

Liam

-- 
Liam Stewart :: Red Hat Canada, Ltd. :: liams@redhat.com

Tue, 13 Nov 2001 16:28:57 -0500
Liam Stewart <liams@redhat.com>
Re: Funny timezone shift causes failure in test suite
[about a failure with a historical date in the JDBC driver's
test suite, when ran in the CET timezone]

On Tue, 13 Nov 2001 16:28:57 -0500, you wrote:
>At least) Sun's JRE seems to be braindead when it comes to
>timezones..  I made a small test program that runs through a sequence of
>years and for each year, it takes a date in the winter and a date in the
>summer and checks whether or not the calendar is in daylight savings
>time. From 1945 to 1977, it reports that in the summer, daylight savings
>time is observed, which it shouldn't be. 
>
>After poking around some, I think that Java isn't concerned with
>historical behaviour (ICU [Internation Components for Unicode] isn't
>AFAICS, and the ICU Java classes developped by Taligent were integrated
>into Sun's JDK 1.1...)

Yes, you're right.

This bug description confirms that the JVM up to 1.3 does not
have a historically correct timezone implementation:
http://developer.java.sun.com/developer/bugParade/bugs/4257314.html
(registration required), and it says this is fixed in 1.4 aka
'merlin'.

I tested it with Sun's JDK 1.4 beta 3 and the Sun's J2EE 1.3
reference implementation on Red Hat Linux 7.1 and indeed the
test suite ran fine with 0 failures (I had to tweak some
unimplemented methods to be able to run the driver with this
JVM/J2EE).

Case closed. We don't need to do anything in the driver. Liam,
thanks a lot for your help!

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Sun, 25 Nov 2001 20:00:29 +0100
Rene Pijlman <rene@lab.applinet.nl>
Re: Funny timezone shift causes failure in test suite