Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] - Mailing list pgsql-hackers
| From | Steve Crawford |
|---|---|
| Subject | Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] |
| Date | |
| Msg-id | CAEfWYyy8m05pJzExL0M=d7V5zH8VaPTnTTerTgiCNxtHN2ZawA@mail.gmail.com Whole thread Raw |
| In response to | Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] (Kevin Grittner <kgrittn@ymail.com>) |
| Responses |
Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]
(Kevin Grittner <kgrittn@ymail.com>)
|
| List | pgsql-hackers |
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.
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@ymail.com> wrote:
On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> 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.
> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
pgsql-hackers by date: