Re: timezone incompatibility

From: Barry Lind <barry(at)xythos(dot)com>
To: "Winter, Wolfgang" <Wolfgang(dot)Winter(at)AtosOrigin(dot)com>
Cc: Thomas Lockhart <lockhart(at)fourpalms(dot)org>, pgsql-jdbc(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: timezone incompatibility
Date: 2002-06-22 03:46:09
Message-ID: 3D13F301.2050603@xythos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-jdbc

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
>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Lockhart 2002-06-22 05:27:24 Re: timezone incompatibility
Previous Message Stephan Szabo 2002-06-22 02:19:36 Re: Unable to run one pl/pgSQL function :-(

Browse pgsql-jdbc by date

  From Date Subject
Next Message Thomas Lockhart 2002-06-22 05:27:24 Re: timezone incompatibility
Previous Message Barry Lind 2002-06-22 03:41:17 Re: How to get multiple result sets