Thread: Date and Time or Timestamp?
Greetings again! I have a small dilemma here and I'm not sure if it's just me making problems or not. My question is this: how far into the future can I expect a Timestamp to be valid? I've seen some conflicting reports ... 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. But Josh Berkus disputes that in his Working with Dates and Times in PostgreSQL FAQ <http://techdocs.postgresql.org/techdocs/faqdatesintervals.php>, claiming timestamps have a useful range of 4713BC to over 100,000AD. So which is right? I think timestamps are easier for me to deal with, since I'm accessing/creating entries from WebObjects (which has a NSTimestamp object with looks like it will match up nicely to postgresql's timestamp), but the thought of dealing with a Y-2037 bug when I'm pushing 70 makes my spine cold. I thought I'd deal with this by separating Dates and Times (shouldn't be a problem ... Dates should be good past 32000AD at least and I don't expect much else to survive that long:-), but that seems much more difficult in the WO context ... Am I making this harder than it should be? Dave Stewart Aqua-flo Supply (Goleta) dstewart@aquaflo.com There are only two industries that refer to their customers as "users". -Edward Tufte
Dave, > 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: staffos=# select version(); version --------------------------------------------------------------- PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC 2.95.3 (1 row) staffos=# select '2099-09-27'::TIMESTAMP; timestamptz --------------------- 2099-09-27 00:00:00 (1 row) staffos=# select '2099-09-27'::TIMESTAMP + '30 days'::INTERVAL; ?column? --------------------- 2099-10-27 00:00:00 (1 row) staffos=# select '2999-09-27'::TIMESTAMP + '30 days'::INTERVAL; ?column? --------------------- 2999-10-27 00:00:00 (1 row) staffos=# select '8999-09-27'::TIMESTAMP + '30 days'::INTERVAL; ?column? --------------------- 8999-10-27 00:00:00 (1 row) I'll admit to not having tested 90,000 AD, but I think we can live with a Y10K bug, don't you? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.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
On Tuesday, April 29, 2003, at 09:48 PM, Tom Lane offered this: > Josh Berkus <josh@agliodbs.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@aquaflo.com The human mind ordinarily operates at only ten percent of its capacity -- the rest is overhead for the operating system.
Tom, > 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: Ah, I see. I don't use timestamp with timezone much; I find the Unix implementation of time zones inadequate for reality. So, the answer is that TIMESTAMP WITH TIMEZONE is good through 2037, and TIMESTAMP WITHOUT TIME ZONE is good through 10,000AD? > 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. And, on the up side, if the various *nixes fix their time zone behaviour past 2037, then Postgres will be automatically fixed as well, yes? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> And, on the up side, if the various *nixes fix their time zone behaviour past
> 2037, then Postgres will be automatically fixed as well, yes?
Depends what the new API looks like. I would think that a sane answer
is to redefine time_t as a signed 64-bit value, preserving the 1/1/1970
zero origin, but who knows what the library people will really do?
glibc's recent move to redefine time_t as unsigned (losing support for
all pre-1970 dates) doesn't give me high confidence in their design
sensibility.
In any case you can bet that we'll have some work to do when a better
API is available.
regards, tom lane