On Monday, November 9, 2015 9:37 AM, Robert Haas <robertmh...@gmail.com> wrote: > On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy <vitaly.buro...@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
