Skip site navigation (1) Skip section navigation (2)

RE: timestamps cannot be created without time zones

From: "Rainer Mager" <rmager(at)vgkk(dot)com>
To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>, <pgsql-bugs(at)postgresql(dot)org>
Subject: RE: timestamps cannot be created without time zones
Date: 2001-08-24 03:53:13
Message-ID: NEBBJBCAFMMNIHGDLFKGGEHOENAA.rmager@vgkk.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)postgresql(dot)org
> [mailto:pgsql-bugs-owner(at)postgresql(dot)org]On Behalf Of Peter Eisentraut
> Sent: Friday, August 24, 2001 12:15 AM
> To: rmager(at)vgkk(dot)com; pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] timestamps cannot be created without time zones
>
>
> > Rainer Mager (rmager(at)vgkk(dot)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(at)gmx(dot)net   http://funkturm.homeip.net/~peter


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-08-24 04:20:06
Subject: Re: timestamps cannot be created without time zones
Previous:From: Tom LaneDate: 2001-08-24 03:25:42
Subject: Re: Bug #425: Upper(), Lower() bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group