Re: converting between infinity timestamp and float8 (epoch)

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