Horton
Menu

Thread

  1. timestamps cannot be created without time zones

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-08-23T00:17:36Z

    Rainer Mager (rmager@vgkk.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
timestamps cannot be created without time zones

Long Description
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.

Sample Code
vgkk=> create table a ( t1 timestamp, t2 timestamp with time zone, t3 timestamp without time zone );
CREATE
vgkk=> \d a
                    Table "a"
 Attribute |           Type           | Modifier 
-----------+--------------------------+----------
 t1        | timestamp with time zone | 
 t2        | timestamp with time zone | 
 t3        | timestamp with time zone | 


No file was uploaded with this report

  2. Re: timestamps cannot be created without time zones

    Peter Eisentraut <peter_e@gmx.net> — 2001-08-23T15:15:18Z

    > 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

  3. RE: timestamps cannot be created without time zones

    Rainer Mager <rmager@vgkk.com> — 2001-08-24T03:53:13Z

    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

  4. Re: timestamps cannot be created without time zones

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-24T04:20:06Z

    "Rainer Mager" <rmager@vgkk.com> writes:
> The Postgres docs mention the the precision/range of timestamp with and
> withount a timezone is different.

Where are you reading that, exactly?  Since there isn't any difference
between "timestamp with and without a timezone", that can't possibly be
what was meant --- so we need to fix/clarify the documentation.
Please tell us where.

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

Timezone handling in PG is dependent on the underlying OS' timezone
database, which doesn't go back further than 1901 in any Unix that 
I've heard of.  You should realize of course that the very notion of
timezone was only standardized in the 1800s, so attaching a timezone
to dates much older than that would be a dubious practice anyway...

			regards, tom lane

  5. Re: timestamps cannot be created without time zones

    Peter Eisentraut <peter_e@gmx.net> — 2001-08-24T08:50:53Z

    Tom Lane writes:

> Timezone handling in PG is dependent on the underlying OS' timezone
> database, which doesn't go back further than 1901 in any Unix that
> I've heard of.  You should realize of course that the very notion of
> timezone was only standardized in the 1800s, so attaching a timezone
> to dates much older than that would be a dubious practice anyway...

Thus Rainer's point is that when having times both before and after 1901
in the same data set you get inconsistencies.  This seems like a good
reason to introduce a true 'timestamp without time zone' type.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  6. Re: timestamps cannot be created without time zones

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-08-24T14:55:17Z

    > Tom Lane writes:
> 
> > Timezone handling in PG is dependent on the underlying OS' timezone
> > database, which doesn't go back further than 1901 in any Unix that
> > I've heard of.  You should realize of course that the very notion of
> > timezone was only standardized in the 1800s, so attaching a timezone
> > to dates much older than that would be a dubious practice anyway...
> 
> Thus Rainer's point is that when having times both before and after 1901
> in the same data set you get inconsistencies.  This seems like a good
> reason to introduce a true 'timestamp without time zone' type.

Let me see if I follow here.  If I am in the Eastern timezone and enter
a time for 9pm, 1/1/1850, and someone else in the Central timezone
enters the same time, if I look at the two dates from the Eastern
timezone I will see mine as 9pm and the other as 10pm?

Wow, I wonder if that is bad? 

It seems I would mix these in a table so it is the underlying data
representation that may be the problem.  Yikes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  7. Re: timestamps cannot be created without time zones

    Peter Eisentraut <peter_e@gmx.net> — 2001-08-24T15:47:06Z

    Bruce Momjian writes:

> Let me see if I follow here.  If I am in the Eastern timezone and enter
> a time for 9pm, 1/1/1850, and someone else in the Central timezone
> enters the same time, if I look at the two dates from the Eastern
> timezone I will see mine as 9pm and the other as 10pm?

That's exactly what happens, only that the central time would probably
show 8pm.

> Wow, I wonder if that is bad?

Depends on the application.  I do wonder how the backend gets to know the
time zone from the front end.  export PGTZ seems to do that, but not
export TZ, which is the standard variable.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  8. Re: timestamps cannot be created without time zones

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-24T19:52:31Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> Let me see if I follow here.  If I am in the Eastern timezone and enter
>> a time for 9pm, 1/1/1850, and someone else in the Central timezone
>> enters the same time, if I look at the two dates from the Eastern
>> timezone I will see mine as 9pm and the other as 10pm?

> That's exactly what happens, only that the central time would probably
> show 8pm.

No, that is *not* what happens.  For dates outside the range known to
the local platform's timezone database, we effectively assume that all
times are given in GMT; no timezone corrections are applied on either
input or output.  For example:

regression=# create table history(f1 timestamp);
CREATE
regression=# show TimeZone ;
NOTICE:  Time zone is EST5EDT
SHOW VARIABLE
regression=# insert into history values(now());
INSERT 925967 1
regression=# insert into history values('1810-01-01 12:00');
INSERT 925968 1
regression=# select * from history;
           f1
------------------------
 2001-08-24 15:39:45-04
 1810-01-01 12:00:00
(2 rows)

regression=# set TimeZone TO 'pst8pdt';
SET VARIABLE
regression=# select * from history;
           f1
------------------------
 2001-08-24 12:39:45-07
 1810-01-01 12:00:00
(2 rows)

regression=#

The fact that such times are taken as GMT can be proven with some
experiments near the boundary, eg (back to EST timezone here for
display):

regression=# select '1901-12-14 1:00 gmt'::timestamp;
        ?column?
------------------------
 1901-12-13 20:00:00-05
(1 row)

regression=# select '1901-12-14 1:00 gmt'::timestamp - '1 hour'::interval;
        ?column?
------------------------
 1901-12-13 19:00:00-05
(1 row)

regression=# select '1901-12-14 1:00 gmt'::timestamp - '2 hour'::interval;
      ?column?
---------------------
 1901-12-13 23:00:00
(1 row)

regression=#

While this is a little disconcerting, I am not sure how we can do
any better.  Certainly adding or subtracting the current local timezone
offset wouldn't be an improvement (in this example, I'd certainly not
want to suppose that EDT -04 offset should apply to a wintertime date
in 1901, even if I was willing to assume that Eastern time was okay
otherwise).

What it comes down to is that our timestamp datatype *does* have
"timestamp without timezone" behavior for dates outside the range of
known timezone data.

			regards, tom lane

  9. Re: timestamps cannot be created without time zones

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-08-24T20:02:09Z

    > Peter Eisentraut <peter_e@gmx.net> writes:
> > Bruce Momjian writes:
> >> Let me see if I follow here.  If I am in the Eastern timezone and enter
> >> a time for 9pm, 1/1/1850, and someone else in the Central timezone
> >> enters the same time, if I look at the two dates from the Eastern
> >> timezone I will see mine as 9pm and the other as 10pm?
> 
> > That's exactly what happens, only that the central time would probably
> > show 8pm.
> 
> No, that is *not* what happens.  For dates outside the range known to
> the local platform's timezone database, we effectively assume that all
> times are given in GMT; no timezone corrections are applied on either
> input or output.  For example:

Nifty trick.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  10. Re: timestamps cannot be created without time zones

    Peter Eisentraut <peter_e@gmx.net> — 2001-08-24T22:22:00Z

    Tom Lane writes:

> No, that is *not* what happens.  For dates outside the range known to
> the local platform's timezone database, we effectively assume that all
> times are given in GMT; no timezone corrections are applied on either
> input or output.

Oh, I wasn't sure whether Bruce meant the behaviour in 1850 exactly.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  11. RE: timestamps cannot be created without time zones

    Rainer Mager <rmager@vgkk.com> — 2001-08-26T23:16:04Z

    Ok, I now understand more about how Postgres handles these older dates but
we're still seeing a problem that I'm not sure how to handle. Simply put, we
write a TIMESTAMP via JDBC and then read it back. What we write and what we
read are different. The only way I can think of fixing the problem is having
a check, in our code, to see if the dates are earlier than Postgres' magic
cutoff day, and, if so, do timezone fixes. This is bad. Is there a better
way. Is there, perhaps, a bug in the JDBC driver?


Thanks,

--Rainer