Skip site navigation (1) Skip section navigation (2)

Re: Date and Time or Timestamp?

From: Dave Stewart <dstewart(at)aquaflo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, josh(at)agliodbs(dot)com,pgsql-novice(at)postgresql(dot)org
Subject: Re: Date and Time or Timestamp?
Date: 2003-04-30 15:23:00
Message-ID: A0B81B57-7B1F-11D7-9517-0050E4904947@aquaflo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.


In response to

pgsql-novice by date

Next:From: Josh BerkusDate: 2003-04-30 15:30:05
Subject: Re: Date and Time or Timestamp?
Previous:From: William laloyauDate: 2003-04-30 15:02:53
Subject: file plpgsql.so missing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group