Re: Date and Time or Timestamp?

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> On CMD's Practical Postgres page
>> <http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x2632%2ehtm>,
>> Table 3-14 claims a timestamp has a range of 1903AD to 2037AD.

> Not at all correct, at least since 7.2.x:

The issue is not really what the raw timestamp value's range is.
The issue is what range of dates do you have local timezone information
for. Pay close attention to the difference here:

regression=# select '1999-09-27'::timestamp with time zone;
timestamptz
------------------------
1999-09-27 00:00:00-04
(1 row)

regression=# select '2999-09-27'::timestamp with time zone;
timestamptz
---------------------
2999-09-27 00:00:00
(1 row)

PG is refusing to assign a time zone to the latter. The reason: our
present code relies on the surrounding Unix system to provide timezone
data, and it does so through Unix APIs that (on most boxen) overflow in
2038. Thus the above behavior.

However, before panicking over that limitation, you should ask yourself
what you will bet that the politicians in your country won't have
changed your daylight-savings rules in the next 35 years. Or for that
matter, do you know when the next leap-second insertion will be, or if
there will be any more at all? We may know now how far away we think
"May 1, 10000 AD" is, but what are the odds that people in 10000 AD will
still use the Gregorian calendar (which is less than 400 years old IIRC)?
Civil calendars both past and future are so uncertain that you shouldn't
get too excited about these issues...

regards, tom lane