Re: Handling Time - Mailing list pgsql-novice

Hi,

Don't worry about the 'load', it's peanuts to pg.

Here's something on the subject from the past.

BR,

Aarni

>>>> begin quote >>>>

> 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...


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?

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.

>>>> end quote >>>>

On Friday 01 April 2005 08:20, you wrote:
> i'm wanting to timestamp database entries and i would
> appreciate some feedback from the collective wisdom of
> this thread.
>
> what is the best way to do this?  are ther pitfalls to
> avoid.
>
> i have started using date, but thought there was a
> remote chance i might need time, also.  is the db load
> of storing time great enough that it isn't worth
> storing time based on remote chances it *might* be
> valuable?
>
> tia...
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Personals - Better first dates. More second dates.
> http://personals.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------