RE: timestamps cannot be created without time zones

Thanks for the info. I'm unsure on a few things, though.

The Postgres docs mention the the precision/range of timestamp with and
withount a timezone is different. Are you saying that, because they are,
internally, the same the precision/range is also the same?

Also, we have seen a bug regarding time stamps when they are set via JDBC.
We thought that it might be a core Postgres bug but after playing with psql
a bit I think it may be in the JDBC driver.

What happens is that when a timestamp is written to the database, if the
timestamp is earlier than 1901 (I don't know the exact cutoff date), the the
time is munged at some point. Via psql I verified that this date is
important:

 1901-08-08 01:00:00
 1902-08-08 01:00:00+09

Notice that the 1901 date looses the timezone. Via JDBC, these older date
are not properly handled athe the +09 hours of the timezone are not properly
accounted for. Specifically, if I insert (via JDBC) '1-Jan-1900 :00:00:00'
and then select it back out I get '31-Dec-1900:15:00:00'. This is not a good
thing.

Should I enter a new bug?


Thanks,

--Rainer


> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org
> [mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Peter Eisentraut
> Sent: Friday, August 24, 2001 12:15 AM
> To: rmager@vgkk.com; pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] timestamps cannot be created without time zones
>
>
> > Rainer Mager (rmager@vgkk.com) reports a bug with a severity of 2
>
> > Creating a TABLE with a TIMESTAMP type seems to ignore the WITH TIME
> > ZONE option. That is, with or without the option the TIMESTAMP still
> > has a time zone.
>
> We feel that the SQL timestamp definition as regards with/without time
> zone is undesirable.  Our timestamp type always stores the time in UTC and
> prints it out according to the local time zone.  The fact that the type
> then comes out as 'timestamp with time zone' is disputed, but they are in
> fact one and the same type.
>
> --
> Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter