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

On Jul 13, 2011, at 1:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
> 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?

It's sort of non-obvious that either behavior is better than the other. We might just be replacing one surprising
behaviorwith another. 

...Robert