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

On 11/9/15, 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.
>> 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
>

I apologize for the late answer: I was very sick last week.

So, summarizing answers to the table:
|Inf|NULL|NaN|Err
Danielle Varrazzo | + | | |
Bruce Momjian | + | | |
Robert Haas | | | | -
Alvaro Herrera | | | |
Brendan Jurd | | | | +
Tom Lane | | | + | +
Josh Berkus | | | |

Kevin Grittner | | + | |
Tom Lane | | + | - | -
Steve Crawford | | + | |
Torsten Zuehlsdorff | | + | |
Total: 2 4 0 0

Majority of the votes for NULL for "other things" except epoch.
Nobody answers about differences between monotonic and oscillating values.

I suppose behavior of monotonic values (julian, century, decade,
isoyear, millennium and year) should be the same as for epoch (which
obviously also monotonic value).
Proposed patch has that behavior: +/-infinity for epoch, julian,
century, decade, isoyear, millennium and year; NULL for other fields.

P.S.: I've just found out there is no explanation of "JULIAN" unit in
the documentation of extracting at all. In the other parts of the
documentation there is only history of Julian Date system, inputs and
formatting.
--
Best regards,
Vitaly Burovoy