Re: timestamps cannot be created without time zones

Peter Eisentraut <peter_e(at)gmx(dot)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