timestamp not consistent with documentation or standard

Started by Dave Martinalmost 25 years ago2 messageshackers
Jump to latest
#1Dave Martin
xxiii@cyberdude.com

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dave Martin (#1)
Re: timestamp not consistent with documentation or standard

Dave Martin <xxiii@cyberdude.com> writes:

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.

Actually, what you should have done was consult the archives of this
list. You will find that you have wandered into the no man's land
of an armed conflict :-(. Unless you have some new argument that will
persuade one camp or the other to concede, it's unlikely that the
naming of the timestamp type (there is only one, and no visible interest
in implementing more) will change soon.

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

This is definitely a bug --- looks like EncodeDateTime fails to consider
the possibility that the output of sprintf will be longer than "normal".
Will fix.

regards, tom lane