Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] - Mailing list pgsql-hackers

On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly.burovoy@gmail.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.
> http://www.postgresql.org/docs/9.5/static/functions-datetime.html
>
>
> There was a discussion ended in nothing. It began at:
> http://www.postgresql.org/message-id/CA+mi_8bda-Fnev9iXeUbnqhVaCWzbYhHkWoxPQfBca9eDPpRMw@mail.gmail.com
>
> Discussants agreed change is necessary, but couldn't decide what
> behavior is preferred: throwing an error or returning NULL, NaN or +/-
> infinity.
>
> My thoughts about that cases:
> * Throwing an error: prefer to avoid it according to
> http://www.postgresql.org/message-id/73A5666E-2D40-457E-9DFF-248895DB7FAF@gmail.com
> * NULL: it is "absence of any value", i.e. it could be returned iff
> input value is NULL (in the other case it is not better than returning
> 0).
> * NaN: it could be returned if value is outside current axe (like
> complex value), but it is not the case.
>
> In a parallel discussion ("converting between infinity timestamp and
> float8 (epoch)")
> http://www.postgresql.org/message-id/CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com
> There was interesting thought to make difference between monotonic
> values (century, decade, epoch, isoyear, millennium and year) and
> oscillating values (day, dow, doy, hour, isodow, microseconds,
> milliseconds, minute, month, quarter, second and week).
> An argument is for monotonic values +/- infinity has a sense, but not
> for oscillating ones.
> But for oscillating values NULL was proposed, that (IMHO) is not a
> good idea (see above).
> I think changing current mark "input value is not finite" allows an
> app layer (which knows which field it tries to fetch from
> timestamp[tz]) to handle extracted value correctly. For oscillating
> values there can be the same values as for monotonic values, because
> you can't mix them up.
> The end of the parallel discussion (with the most important thoughts)
> at http://www.postgresql.org/message-id/4EFCFD1C.8040001@archidevsys.co.nz
>
> So I think +/- infinity is the best returning value for all fields.
>
> The attached patch contains changes in timestamp_part and
> timestamptz_part and tests for them.
>
> I doubt whether it can be backpatched (according to team's rules) or
> not, but the patch can be applied down to 9.2 without conflicts and
> passes tests.
> Unfortunately, on 9.1 proposed test fails because "SELECT
> EXTRACT(EPOCH FROM DATE '1970-01-01')" gives "28800" instead of "0".
> Before 9.2 it was time zone-related.

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company