Re: Can't figure out how to use now() in default for tsrange column (PG 9.2)

On Mon, Jul 16, 2012 at 7:08 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> Given:
> A) extract() returns a "double precision"
> B) "... In addition to ordinary numeric values, the floating-point types
> have several special values: ... 'Infinity ..."
>
> I would vote that the epoch result should be "Infinity".

That's what I think the only reasonable value if extract(epoch) was to
be used to map timestamps to doubles: the order relation is preserved,
hence indexes work as they should. Then, of course, I may be asking
too much from that function (although in that case the need for a
purposely designed mapping function would be felt. At least, I've felt
it).

> Specific, but unknown (e.g., day of week, month, year, etc...) results could
> return "NaN" though "NULL" is also, probably more, reasonable given the
> context.
>
> The goal would be to use "Infinity" in case where "<>" comparisons are
> common and use "NULL" where "=" comparisons are common.
>
> Personally I'd prefer to generate an error in places where "NULL" would be
> the result in order to minimize bugs.

The use case of extracting anything else than epoch from infinity is
not so compelling. And of course the result is undetermined. Having to
choose one, I'd go for the same result of sin(inf), which is NaN.

Even without this quirk, the problem of mapping timestamps to other
languages data types could be an even stronger design factor. I've
personally settled for 9999-12-31 which is python's datetime.max, maps
ok to doubles and won't create problems for almost 8000 years.

-- Daniele