Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]

I was unaware that we had +- infinity for numeric.

select pg_typeof(extract(epoch from current_date));
pg_typeof
------------------
double precision

Given that null is a "special value that is used to indicate the absence of
any data value" and that attributes like month or day-of-week will have no
value for a date of infinity I'd be OK with returning null.

I suppose the real question is what return value will cause the smallest
amount of breakage and surprising results. Throwing an error will
definitely break legit queries.

Cheers,
Steve

On Mon, Nov 9, 2015 at 8:22 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> > On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>
> wrote:
>
> >> I'd like to raise a topic about extracting fields from infinite
> >> timestamps, so much more that it is mentioned in the TODO list:
> >> "Determine how to represent date/time field extraction on infinite
> >> timestamps".
> >>
> >> Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives
> >> result "0" as a mark it has "special" input value.
> >>
> >> The most confusing case is 'epoch' field: returning "0" from
> >> "infinity" means the same thing as returning "0" from "1970-01-01+00".
> >>
> >> Returning zero in most other cases is only slightly less confusing
> >> (may be because for me they are less often used).
> >> For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP
> >> 'Infinity')" with result 0, as if it is Sunday?
> >> The same thing with fields: decade, hour, minute, seconds,
> >> microseconds, milliseconds, timezone, timezone_hour, timezone_minute.
> >> Also for "millennium" and "year" (with the note "Keep in mind there is
> >> no 0 AD") current returning value is _between_ allowed values, but
> >> disallowed.
>
> > We're definitely not going to back-patch this. Let's tally up the
> > votes on that other thread:
> >
> > Danielle Varrazzo: infinity
> > Bruce Momjian: infinity
> > Robert Haas: not sure we want to change anything, but if so let's
> > definitely NOT throw an error
> > Alvaro Herrera: infinity for epoch, but what about other things?
> > Brendan Jurd: infinity for epoch, error for other things
> > Tom Lane: infinity for epoch, error or NaN for other things
> > Josh Berkus: definitely change something, current behavior sucks
> >
> > That doesn't seem like enough consensus to commit this patch, which
> > would change everything to +/-infinity. That particular choice
> > wouldn't bother me much, but it sounds like other people aren't sold.
> > I think we need to try to hash that out a little more rather than
> > rushing into a backward-incompatible change.
>
> I agree that none of this should be back-patched.
>
> I agree that a timestamp[tz] of infinity should yield infinity for
> epoch.
>
> My first choice for other things would be NaN, but throwing an
> error instead would be OK.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>