Re: Timezones and time/timestamp values in FE/BE protocol

On Sun, 09 Sep 2001 13:38:52 -0700, you wrote:
[...]
Thanks for your explanation. This helps a lot.

>If you could post a bit more about the issue you are having
>I might be able to be more specific.

I'm looking at the 4 remaining failures of our own JDBC test
suite. They all have to do with timestamps and times, and they
are all caused by a 1 hour shift between the expected value and
the actual value. I run both the backend and the JVM on the same
Linux test server.

Its located in Amsterdam, The Netherlands, Central European
Daylight Savings Time (CETDST, UTC+2, GMT+2). I always thought I
was in CET=GMT+1, but now the offset is 2, because of daylight
saving time (whoever invented that should be #!$^&). Perhaps I
should go live in Greenwich, they don't seem to have daylight
saving time overthere.

In psql I see:
    show timezone;
    NOTICE:  Time zone is unset

Here is some detailed information about the failures. I'm
refering to line numbers in 7.2 current CVS:
TimeTest.java       revision 1.1
TimestampTest.java  revision 1.2

1) TimeTest.java:89

getHours(t) expected 1, actual 0
t.toString() returns the expected "01:02:03", but this is
because java.sql.Time.toString() converts to the JVM's timezone.

2) TimeTest.java:96

getHours(t) expected 23, actual 0
t.toString returns "00:59:59"

3) TimestampTest.java:115

Expected: getTimestamp(1970,6,2,8,13,0) returns "1970-06-02
08:13:00.0"
Actual: t.toString() returns "1970-06-02 09:13:00.0"

4) TimestampTest.java:115 (second time around)

Expected: getTimestamp(1970,6,2,8,13,0) returns "1970-06-02
08:13:00.0"
Actual: t.toString() returns "1970-06-02 07:13:00.0"

My first impression is that in all cases a timezone shift is
applied in only one direction (store vs. retrieve). The cause
might also be a problem with daylight saving time, there are
some comments about that in TimestampTest.java.

Up till now I've managed without a graphical debugger, but to
get a good feel for what's happening between the test code and
the wire I think it'll be easier to setup JBuilder with the
driver and step through the code.

But now its almost bedtime in my timezone, and you never know
with these mailing lists. Sometimes the solution is in your
inbox when you wake up :-)

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