Re: converting between infinity timestamp and float8 (epoch)

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>Phil Sorber<phil(at)omniti(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +