Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] - Mailing list pgsql-hackers
From | Kevin Grittner |
---|---|
Subject | Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] |
Date | |
Msg-id | 393841176.1658734.1447086152230.JavaMail.yahoo@mail.yahoo.com Whole thread Raw |
In response to | Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] (Robert Haas <robertmhaas@gmail.com>) |
Responses |
Re: Extracting fields from 'infinity'::TIMESTAMP[TZ]
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Extracting fields from 'infinity'::TIMESTAMP[TZ] (Steve Crawford <scrawford@pinpointresearch.com>) |
List | pgsql-hackers |
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
pgsql-hackers by date: