Re: timezone incompatibility - Mailing list pgsql-general

OK, I found the bug in the jdbc code.  It was treating a date coming
from the server without timezone information as being in the local
timezone instead of being in GMT.  I will fix this over the weekend.

thanks,
--Barry


Thomas Lockhart wrote:

>>This isn't a jdbc issue so I am forwarding to general.
>>
>>
>
>Hmm. Why is it not a jdbc issue? There is a documented, consistant, and
>altogether reasonable behavior from the database (imho of course ;). If
>there is a Java spec that somehow time zones must be respected for
>decades and centuries when there was no such concept even defined, then
>it would seem to fall on jdbc to implement that idea.
>
>The symptom is that a time from 1756 is entered with an explicit time
>zone. PostgreSQL accepts that. On query, the time is returned in GMT,
>without any claim for a time zone at all:
>
>lockhart=# select timestamp with time zone
>lockhart-# 'Sun Jun 20 20:16:54 CEST 1756';
>     timestamptz
>---------------------
> 1756-06-20 18:16:54
>
>So although the date is not returned with an explicit "GMT" for the time
>zone, it is not claiming to be CEST either.
>
>What different behavior would we need to get JDBC to infer the expected
>result? It may be we need to change *both* JDBC and PostgreSQL, but
>certainly changing just PostgreSQL won't fix the problem (at least
>without introducing troubles for others).
>
>                  - Thomas
>
>
>
>>>I'm testing our auto-configuration persistence framework (acp) against
>>>several databases and now that it comes to PostgreSQL, I run into a timezone
>>>incompatibility compared to other jdbc-drivers/databases. The framework
>>>tests the database specific datatypes by inserting a value, retrieving it
>>>and comparing the result. Here is the result for timestamp:
>>>
>>>Insert and Retrieve of SQLType 93 test value <Sun Jun 20 20:16:54 CEST
>>>1756> failed. Retrieved after insert: <Sun Jun 20 18:16:54 CEST 1756>
>>>
>>>I tried it with a test value in 1992 and the test passes.
>>>Okay, the docs say:
>>>"PostgreSQL uses your operating system's underlying features to provide
>>>output time-zone support, and these systems usually contain information for
>>>only the time period 1902 through 2038 (corresponding to the full range of
>>>conventional Unix system time)."
>>>
>>>But nevertheless, I feel sick with this behaviour, to me it seems not to be
>>>correct and it makes PostgreSQL incompatible to other databases. The
>>>databases I have tested so far retrieve the correct date  before 1902.
>>>
>>>
>>>regards
>>>Wolfgang
>>>
>>>
>>>
>>>Dr. Wolfgang Winter
>>>LogiTags Systems
>>>http://www.logitags.com
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 4: Don't 'kill -9' the postmaster
>>>
>>>
>>>
>>>
>>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>