timestamp not consistent with documentation or standard

From: Dave Martin <xxiii(at)cyberdude(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: timestamp not consistent with documentation or standard
Date: 2001-07-09 23:26:55
Message-ID: 3B4A3DBF.9CC1D0AD@cyberdude.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Ok, i've been told to bring this up on this mailing list, so, I do so:

rather than kill myself re-explaining, i'll just cut&paste my email
correspondence.

I said:
can't create timestamp field (only timestamp with time zone)

the response was:

Those are the same data types.

then I said:
xxiii writes:

> Well, please document it as such then, as SQL definately implies that
> they are not the same (admittedly, i'm using SQL92 and not SQL99,
which I
> don't have a copy of), as does postgres's documentation, and also the
> fact that one can create a time field with and without timezone.
>
>
http://postgresql.crimelabs.net/users-lounge/docs/7.1/user/datatype-datetime.html

>
> This is very confusing, as is the fact that pre 7.1 postgres shows
> "timestamp" and 7.1 shows "timestamp with time zone", neither version
> seems to be willing to create the other variant (presumably because
they
> are really the same as far as postgres (but not its documentation) are

> concerned). I definately need the "without time zone" behaviour and
> range.
>
> If postgres is really using the same type internally to implement both

> behaviours that should be documented, along with how it works.
>
> I've just done some additional testing, and find that the timestamp
type
> does appear to support the wider range, and is acting like the
"without
> time zone" version, in spite of "\d table" saying "with time zone".
> However its output incorrectly, when years exceed 10000.
>
> insert into test values('05-05-12080', '05-05-12080 1:1:1-7:00');
> insert into test values('05-05-12080', '05-05-12080 1:1:1+7:00');
>
> select * from test;
> w | o
> ---------------------+---------------------
> 2080-05-05 00:00:00 | 2080-05-05 00:00:00
> 2080-05-05 00:00:00 | 2080-05-05 08:01:01
> 12080-05-05 00:0000 | 12080-05-05 08:0101
> 12080-05-05 00:0000 | 12080-05-04 18:0101
> (4 rows)

then I got told to bring it up here.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joseph Shraibman 2001-07-09 23:48:41 Re: Backups WAS: 2 gig file size limit
Previous Message Peter Eisentraut 2001-07-09 21:44:27 Re: AW: New SQL Datatype RECURRINGCHAR