Re: Date and Time or Timestamp?

On Tuesday, April 29, 2003, at 09:48 PM, Tom Lane offered this:

> 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

Thanks Tom and Josh!

Let me make absolutely sure I understand the point Tom is making here.
It looks like the timestamp in Postgres will happily handle dates past
2038, but due to limitations in the Unix system it may not handle time
*ZONES* after that time.

That wouldn't be a problem for me, I'm not paying attention to the time
zone anyway.

Many thanks once again. Let me know if I missed something ....

Dave Stewart
Aqua-flo Supply (Goleta)
dstewart(at)aquaflo(dot)com

The human mind ordinarily operates at only ten percent of its
capacity -- the rest is overhead for the operating system.