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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +