Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 01/22/2016 04:28 AM, Tom Lane wrote: > Vik Fearing writes: >> I looked around for other places where this code should be used and >> didn't find any. I am marking this patch Ready for Committer. > > I pushed this with some adjustments, mainly to make sure that the > erroneous-units errors exactly match those that would be thrown in > the main code line. Thanks! -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 1/21/16, Tom Lane wrote: > Vik Fearing writes: >> I looked around for other places where this code should be used and >> didn't find any. I am marking this patch Ready for Committer. > > I pushed this with some adjustments, mainly to make sure that the > erroneous-units errors exactly match those that would be thrown in > the main code line. > > regards, tom lane Thank you! I didn't pay enough attention to it at that time. -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
Vik Fearing writes: > I looked around for other places where this code should be used and > didn't find any. I am marking this patch Ready for Committer. I pushed this with some adjustments, mainly to make sure that the erroneous-units errors exactly match those that would be thrown in the main code line. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 01/05/2016 09:07 AM, Vitaly Burovoy wrote: > On 1/4/16, Alvaro Herrera wrote: >> It seems we got majority approval on the design of this patch, and no >> disagreement; the last submitted version appears to implement that. >> There's no documentation change in the patch though. I'm marking it as >> Waiting on Author; please resubmit with necessary doc changes. > > Thank you! > Version 3 of the patch with touched documentation in the attachment. > > I decided to mark it as a note, because that separation > (monotonic/oscillation fields) is not obvious and for most values the > function "extract" works as expected (e.g. does not give an error) > until special values are (casually?) passed. I have reviewed this patch. It applies and compiles cleanly and implements the behavior reached by consensus. The documentation is a little light, but I don't see what else needs to be said. The code is clean and well commented. All extraction options are supported. Regression tests are present and seemingly complete. I looked around for other places where this code should be used and didn't find any. I am marking this patch Ready for Committer. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 1/4/16, Alvaro Herrera wrote: > Vitaly Burovoy wrote: > >> Majority of the votes for NULL for "other things" except epoch. >> Nobody answers about differences between monotonic and oscillating >> values. >> >> I suppose behavior of monotonic values (julian, century, decade, >> isoyear, millennium and year) should be the same as for epoch (which >> obviously also monotonic value). >> Proposed patch has that behavior: +/-infinity for epoch, julian, >> century, decade, isoyear, millennium and year; NULL for other fields. > > It seems we got majority approval on the design of this patch, and no > disagreement; the last submitted version appears to implement that. > There's no documentation change in the patch though. I'm marking it as > Waiting on Author; please resubmit with necessary doc changes. > > Thanks, > > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > Thank you! Version 3 of the patch with touched documentation in the attachment. I decided to mark it as a note, because that separation (monotonic/oscillation fields) is not obvious and for most values the function "extract" works as expected (e.g. does not give an error) until special values are (casually?) passed. -- Best regards, Vitaly Burovoy extract_from_infinite_timestamp-v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
Vitaly Burovoy wrote: > Majority of the votes for NULL for "other things" except epoch. > Nobody answers about differences between monotonic and oscillating values. > > I suppose behavior of monotonic values (julian, century, decade, > isoyear, millennium and year) should be the same as for epoch (which > obviously also monotonic value). > Proposed patch has that behavior: +/-infinity for epoch, julian, > century, decade, isoyear, millennium and year; NULL for other fields. It seems we got majority approval on the design of this patch, and no disagreement; the last submitted version appears to implement that. There's no documentation change in the patch though. I'm marking it as Waiting on Author; please resubmit with necessary doc changes. Thanks, -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 17.11.2015 09:09, Vitaly Burovoy wrote: I suppose behavior of monotonic values (julian, century, decade, isoyear, millennium and year) should be the same as for epoch (which obviously also monotonic value). Proposed patch has that behavior: +/-infinity for epoch, julian, century, decade, isoyear, millennium and year; NULL for other fields. This works for me. Greetings, Torsten -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
Jim Nasby writes: > On 11/17/15 2:09 AM, Vitaly Burovoy wrote: >> Proposed patch has that behavior: ±infinity for epoch, julian, >> century, decade, isoyear, millennium and year; NULL for other fields. > What's the logic behind NULL here? Infinity is infinity, whether it's > minutes or years. Didn't you follow the upthread discussion? Fields such as "minutes" are cyclic, so it's impossible to say either that they converge to a defined limit or diverge to infinity as x increases. NULL, in the sense of "unknown", seems like a reasonable representation of that. Infinity doesn't. > My specific fear is that now people will have to do a bunch of IF > timestamp IS NOT NULL THEN ... to get the behavior they need. Considering that the old behavior is to return zero, and we've had relatively few complaints about that, I doubt very many people are going to care. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On Tue, Nov 17, 2015 at 10:57 AM, Tom Lane wrote: > Vitaly Burovoy writes: >> I suppose behavior of monotonic values (julian, century, decade, >> isoyear, millennium and year) should be the same as for epoch (which >> obviously also monotonic value). >> Proposed patch has that behavior: +/-infinity for epoch, julian, >> century, decade, isoyear, millennium and year; NULL for other fields. > > Works for me. Same here. -- Robert Haas EnterpriseDB: 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
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
Vitaly Burovoy writes: > I suppose behavior of monotonic values (julian, century, decade, > isoyear, millennium and year) should be the same as for epoch (which > obviously also monotonic value). > Proposed patch has that behavior: +/-infinity for epoch, julian, > century, decade, isoyear, millennium and year; NULL for other fields. Works for me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 11/17/15 2:09 AM, Vitaly Burovoy wrote: I suppose behavior of monotonic values (julian, century, decade, isoyear, millennium and year) should be the same as for epoch (which obviously also monotonic value). Proposed patch has that behavior: ±infinity for epoch, julian, century, decade, isoyear, millennium and year; NULL for other fields. What's the logic behind NULL here? Infinity is infinity, whether it's minutes or years. It's absolutely NOT the same thing as a NULL timestamp. I don't see why the normal constraint of minute < 60 should apply here; infinity isn't a normal number. My specific fear is that now people will have to do a bunch of IF timestamp IS NOT NULL THEN ... to get the behavior they need. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 11/9/15, Robert Haas wrote: > On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy > 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. >> 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-fnev9ixeubnqhvacwzbyhhkwoxpqfbca9edpp...@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-248895db7...@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-dkpcvwtuje4jwxnkdajaawp6j...@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.8040...@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. > > 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. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > I apologize for the late answer: I was very sick last week. So, summarizing answers to the table: |Inf|NULL|NaN|Err Danielle Varrazzo| + || | Bruce Momjian| + || | Robert Haas | || | - Alvaro Herrera | || | Brendan Jurd | || | + Tom Lane | || + | + Josh Berkus | || | Kevin Grittner | | + | | Tom Lane | | + | - | - Ste
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On 09.11.2015 17:41, Tom Lane wrote: Kevin Grittner writes: On Monday, November 9, 2015 9:37 AM, Robert Haas wrote: 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. Definitely. I agree that a timestamp[tz] of infinity should yield infinity for epoch. I think everybody is sold on that much. My first choice for other things would be NaN, but throwing an error instead would be OK. Since the function hasn't thrown error for such cases in the past, making it do so now would likely break applications. More than once, we've had to modify functions to avoid throwing errors so that you don't get incidental errors when blindly applying a function to all entries in a column. I think going in the opposite direction would elicit protests. An error will also break legit SQL statements. I could see using NaN except for one thing: it'd mean injecting a rather fundamental dependence on IEEE math into a basic function definition. You can be just about 100% certain that if the SQL committee ever addresses this case, it won't be with NaN. ACK. What about returning NULL for the ill-defined cases? That seems to comport with SQL's notion of NULL as "unknown/undefined". This is the first i would expect in such a case. + 1 for NULL. Greetings, Torsten -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
> On Mon, Nov 9, 2015 at 8:22 AM, Kevin Grittner wrote: >> My first choice for other things would be NaN, but throwing an >> error instead would be OK. On Monday, November 9, 2015 10:41 AM, Tom Lane wrote: > What about returning NULL for the ill-defined cases? That seems > to comport with SQL's notion of NULL as "unknown/undefined". On Monday, November 9, 2015 10:44 AM, Steve Crawford wrote: > 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. NULL seens clearly better than NaN or an error; I wish that had occurred to me before I posted. -- 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
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
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. 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 wrote: > On Monday, November 9, 2015 9:37 AM, Robert Haas > wrote: > > On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy > 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 >
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
Kevin Grittner writes: > On Monday, November 9, 2015 9:37 AM, Robert Haas > wrote: >> 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. Definitely. > I agree that a timestamp[tz] of infinity should yield infinity for > epoch. I think everybody is sold on that much. > My first choice for other things would be NaN, but throwing an > error instead would be OK. Since the function hasn't thrown error for such cases in the past, making it do so now would likely break applications. More than once, we've had to modify functions to avoid throwing errors so that you don't get incidental errors when blindly applying a function to all entries in a column. I think going in the opposite direction would elicit protests. I could see using NaN except for one thing: it'd mean injecting a rather fundamental dependence on IEEE math into a basic function definition. You can be just about 100% certain that if the SQL committee ever addresses this case, it won't be with NaN. What about returning NULL for the ill-defined cases? That seems to comport with SQL's notion of NULL as "unknown/undefined". regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On Monday, November 9, 2015 9:37 AM, Robert Haas wrote: > On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy > 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
Re: [HACKERS] Extracting fields from 'infinity'::TIMESTAMP[TZ]
On Sat, Nov 7, 2015 at 9:47 AM, Vitaly Burovoy 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. > 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-fnev9ixeubnqhvacwzbyhhkwoxpqfbca9edpp...@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-248895db7...@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-dkpcvwtuje4jwxnkdajaawp6j...@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.8040...@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. 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. -- Robert Haas EnterpriseDB: 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