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(at)momjian(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support