From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] |
Date: | 2015-11-09 16:22:32 |
Message-ID: | 393841176.1658734.1447086152230.JavaMail.yahoo@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-11-09 16:41:24 | Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] |
Previous Message | Amit Kapila | 2015-11-09 16:15:39 | Re: Parallel Seq Scan |