Re: [BUGS] extract(epoch from infinity) is not 0

Daniele Varrazzo wrote:
> Hello,
>
> =# select extract(epoch from 'infinity'::timestamp);
> date_part
> -----------
> 0
>
> A better value would be 'infinity'::float8. Ditto for -infinity.
>
> I'm trying to use a box-based index to represent the intervals in a
> table containing a pair of fields date_from, date_to (timestamps),
> where semi-open intervals are represented with +/- infinity. Building
> the boxes using extract(epoch from ...) creates wrong entries as
> semi-open intervals are converted into a box with a corner in (0,0).

Looking at:

timestamptz_part(PG_FUNCTION_ARGS)

I see:

if (TIMESTAMP_NOT_FINITE(timestamp))
{
result = 0;
PG_RETURN_FLOAT8(result);
}

The assumption is that extracting _anything_ from an infinite timestamp
should be zero, but I can see your point that epoch perhaps should be
special-cased to return +/- inifinity.

Does anyone object to changing this?

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

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