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

Excerpts from Robert Haas's message of mié jul 13 16:13:12 -0400 2011:
> On Jul 13, 2011, at 1:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Daniele Varrazzo wrote:

> >> =# select extract(epoch from 'infinity'::timestamp);
> >> date_part
> >> -----------
> >>         0
> >> 
> >> A better value would be 'infinity'::float8. Ditto for -infinity.

> > 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.

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

I don't find the proposed behavior all that suprising, which the
original behavior surely is.  I guess the bigger question is whether the
values that timestamptz_part() returns for other cases (than epoch)
should also be different from 0 when an 'infinity' timestamp is passed.
(In other words, why should 0 be the assumed return value here?)

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support