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

Hackers!

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.
--
Best regards,
Vitaly Burovoy.