Re: converting between infinity timestamp and float8 (epoch) - Mailing list pgsql-bugs
From | Bruce Momjian |
---|---|
Subject | Re: converting between infinity timestamp and float8 (epoch) |
Date | |
Msg-id | 20120205043925.GB19450@momjian.us Whole thread Raw |
In response to | Re: converting between infinity timestamp and float8 (epoch) (Gavin Flower <GavinFlower@archidevsys.co.nz>) |
List | pgsql-bugs |
TODO added: Determine how to represent date/time field extraction on infinite timestamps extract(epoch from infinity) is not 0 converting between infinity timestamp and float8 --------------------------------------------------------------------------- On Fri, Dec 30, 2011 at 12:51:56PM +1300, Gavin Flower wrote: > On 28/12/11 10:43, Phil Sorber wrote: > >On Tue, Dec 27, 2011 at 1:00 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > >>Phil Sorber<phil@omniti.com> writes: > >>>My search foo failed me. Someone just pointed me to a similar > >>>conversation from some months ago: > >>>http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php > >>>I would propose that since we can't know the hour or minute of > >>>infinity that we should return null for those. I think NaN would be > >>>wrong because it is a real number, it's just unknown. If we can just > >>>pass infinity through the function, I think we should. > >>The last thread ended with a request for somebody to think through > >>the behavior for *all* extract field types and make a coherent proposal. > >>I don't think you've really advanced the discussion yet. > >> > >>I think I agree with the position that we shouldn't return 0 unless > >>the correct value actually is 0, but it's not clear to me whether > >>to use NULL or NaN to represent "indeterminate". Traditionally we > >>consider NULL to mean "unknown", but it seems like "what's the hour > >>of an infinite timestamp" is a subtly different sort of situation: > >>it's not unknown, we know perfectly well that it's indeterminate. > >>OTOH, choosing NaN would put a pretty significant dependence on > >>IEEE-float arithmetic into the external specification of timestamps, > >>and I find that a bit worrisome, even though IEEE float arithmetic > >>is nigh universal these days. So maybe splitting hairs like that > >>would be ill-advised. It probably depends also on what you expect > >>people to do with the result of extract() --- NULL would presumably > >>propagate through any additional calculation steps as-is, whereas > >>NaN might have less predictable behavior. > >> > >>There was also some support for throwing an error in the previous > >>thread, though I can't say I like that answer myself. > >> > >> regards, tom lane > >It is my understanding that NULL would be for "unknown" or "undefined" > >and NaN for "indeterminate" as well as some other cases like complex > >numbers. I believe per the standard NaN explicitly includes > >indeterminate forms. But I don't think extract(hour from > >'infinity'::timestamp) is an indeterminate form > >(http://en.wikipedia.org/wiki/Indeterminate_form). It is an > >oscillating function similar to sin(x). Limit of sin(x) as x > >approaches infinity is undefined. To me that points to NULL as the > >appropriate value. > > > >Also, like epoch, the expressions that involve year are not > >oscillating. They are monotonic. the limit of extract(millennium from > >'infinity'::timestamp) is infinity. > > > >I'm not going to claim to be a mathematician, so I concede I might be > >wrong with my thought process here. > > > >Given the preceding is true, my proposal is the following for > >extract() when passed an infinite timestamp: > > > >1) Monotonic values (century, decade, epoch, isoyear, millennium and > >year) we return 'infinity'::float8 signed appropriately. > > > >2) Oscillating values (day, dow, doy, hour, isodow, microseconds, > >milliseconds, minute, month, quarter, second and week) would return > >NULL. > > > >3) timezone, timezone_hour and timezone_minute are almost a separate > >issue since timezone is separate from the value. So we should support > >something like 'infinity-05'::timestamp with time zone. Then the > >timezone stuff would just behave normally. > > > >Currently it does this: > > > >postgres=# select 'infinity+00'::timestamp with time zone; > > timestamptz > >------------- > > infinity > >(1 row) > > > >postgres=# select 'infinity-05'::timestamp with time zone; > >ERROR: invalid input syntax for type timestamp with time zone: "infinity-05" > >LINE 1: select 'infinity-05'::timestamp with time zone; > > ^ > > > Hmm... > > Infinity is conceptually the 'maximum' value possible - or more > pr4ecisely: a value greater than any you can specify a concrete > value for in finite time. > > So I think the appropriate value should be the maximum > representational possibility and should be the same regardless of > time zone, plus any operation such as adding or subtracting finite > values should not change it (arithmetic ops with another 'infinite' > value should be either an error or a NaN/Null). This is to > consistent that with the notion of infinity. > > I would suggest that hh:mm:ss.ssss... > should be: 23:59:59.9999... > > > Cheers, > Gavin > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
pgsql-bugs by date: