Thread: timezone incompatibility
Hi, 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
Wolfgang, This isn't a jdbc issue, I would try the hackers list, or the general list. Out of curiosity are you using RedHat? They broke time. You can try complaining to them if this is the case. Dave On Fri, 2002-06-21 at 02:46, Winter, Wolfgang wrote: > Hi, > > 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 > >
Hi Dave, in my opinion it is a jdbc feature (maybe not a bug), as it is the driver that subtracts the two hours from the test date and writes the changed date into the database. Performing a SELECT on this date doesn't re-add the 2 hours, so I see a wrong date. Other jdbc drivers like for oracle, mysql, sapdb ... write the date as is into the database. Operating system is Suse 7.0. thanks for answering Wolfgang -----Ursprüngliche Nachricht----- Von: Dave Cramer [mailto:Dave@micro-automation.net] Gesendet am: Freitag, 21. Juni 2002 11:14 An: Winter, Wolfgang Cc: pgsql-jdbc@postgresql.org Betreff: Re: [JDBC] timezone incompatibility Wolfgang, This isn't a jdbc issue, I would try the hackers list, or the general list. Out of curiosity are you using RedHat? They broke time. You can try complaining to them if this is the case. Dave On Fri, 2002-06-21 at 02:46, Winter, Wolfgang wrote: > Hi, > > 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 > >
Hi Wolfgang, this sounds more like a bug than a feature. Dave On Fri, 2002-06-21 at 06:05, Winter, Wolfgang wrote: > Hi Dave, > > in my opinion it is a jdbc feature (maybe not a bug), as it is the driver > that subtracts the two hours from the test date and writes the changed date > into the database. Performing a SELECT on this date doesn't re-add the 2 > hours, so I see a wrong date. > > Other jdbc drivers like for oracle, mysql, sapdb ... write the date as is > into the database. > > Operating system is Suse 7.0. > > > thanks for answering > Wolfgang > > > > -----Ursprüngliche Nachricht----- > Von: Dave Cramer [mailto:Dave@micro-automation.net] > Gesendet am: Freitag, 21. Juni 2002 11:14 > An: Winter, Wolfgang > Cc: pgsql-jdbc@postgresql.org > Betreff: Re: [JDBC] timezone incompatibility > > Wolfgang, > > This isn't a jdbc issue, I would try the hackers list, or the general > list. > > Out of curiosity are you using RedHat? They broke time. You can try > complaining to them if this is the case. > > Dave > On Fri, 2002-06-21 at 02:46, Winter, Wolfgang wrote: > > Hi, > > > > 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 > >
This isn't a jdbc issue so I am forwarding to general. --Barry Winter, Wolfgang wrote: >Hi, > >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 > > >
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 > > >
> 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
> >
> >
> >
Barry Lind wrote:
>
> 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.
For "timestamptz" aka TIMESTAMP WITH TIME ZONE if it doesn't have a time
zone then it is GMT. If it is "timestamp" aka TIMESTAMP WITHOUT TIME
ZONE then they all are in the local time zone.
Does the JDBC driver let you distinguish between all of the cases?
- Thomas