Re: [HACKERS] [BUGS] extract(epoch from infinity) is not 0
On 14 July 2011 08:16, Robert Haas wrote: > On Jul 13, 2011, at 4:21 PM, Brendan Jurd wrote: >> Well, for example, how do you go about answering the question "what is >> the day-of-month of the infinite timestamp?" The question is >> nonsense; it doesn't have a defined day of month, so I think we should >> be returning NULL or throwing an error. Returning zero is definitely >> wrong. I think throwing an error is the better way to go, as the user >> probably didn't intend to ask an incoherent question. >> >> It makes sense to special-case 'epoch' because it effectively converts >> the operation into interval math; if we ask "how many seconds from >> 1970-01-01 00:00 UTC until the infinite timestamp?" the answer is >> genuinely "infinite seconds". So +1 for the proposed change for >> epoch, and let's throw an error for the other date fields instead of >> returning zero. > > I'd rather we avoid throwing an error, because that sometimes forces people > who want to handle that case to use a subtransaction to catch it, which is > quite slow. SELECT CASE WHEN isfinite(ts) THEN extract(day from ts) ELSE NULL END Cheers, BJ -- 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] [BUGS] extract(epoch from infinity) is not 0
On Jul 13, 2011, at 4:21 PM, Brendan Jurd wrote: > On 14 July 2011 06:58, Alvaro Herrera wrote: >> I don't find the proposed behavior all that suprising, which the >> original behavior surely is. I guess the bigger question is whether the >> values that timestamptz_part() returns for other cases (than epoch) >> should also be different from 0 when an 'infinity' timestamp is passed. >> (In other words, why should 0 be the assumed return value here?) >> > > Well, for example, how do you go about answering the question "what is > the day-of-month of the infinite timestamp?" The question is > nonsense; it doesn't have a defined day of month, so I think we should > be returning NULL or throwing an error. Returning zero is definitely > wrong. I think throwing an error is the better way to go, as the user > probably didn't intend to ask an incoherent question. > > It makes sense to special-case 'epoch' because it effectively converts > the operation into interval math; if we ask "how many seconds from > 1970-01-01 00:00 UTC until the infinite timestamp?" the answer is > genuinely "infinite seconds". So +1 for the proposed change for > epoch, and let's throw an error for the other date fields instead of > returning zero. I'd rather we avoid throwing an error, because that sometimes forces people who want to handle that case to use a subtransaction to catch it, which is quite slow. If we don't like 0, perhaps NULL or NaN would be better. ...Robert -- 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] [BUGS] extract(epoch from infinity) is not 0
Robert Haas writes: > On Jul 13, 2011, at 1:43 PM, Bruce Momjian wrote: >> I see: >> >> if (TIMESTAMP_NOT_FINITE(timestamp)) >> { >> result = 0; >> PG_RETURN_FLOAT8(result); >> } >> >> Does anyone object to changing this? > It's sort of non-obvious that either behavior is better than the other. We > might just be replacing one surprising behavior with another. Well, this code path is not much except a punt. If we're going to touch it we should think through the behavior for all field types, not just epoch. I think a reasonable case could be made for throwing error or returning NaN (indicating "indeterminate") for most field types. I can see returning +/- infinity for epoch --- are there any others where that's sane? 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] [BUGS] extract(epoch from infinity) is not 0
> It's sort of non-obvious that either behavior is better than the other. Here's the reason why the existing behavior is wrong: postgres=# select extract('epoch' from timestamptz 'infinity') = extract ('epoch' from timestamptz '1970-01-01 00:00:00-00'); ?column? -- t -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] [BUGS] extract(epoch from infinity) is not 0
On 14 July 2011 06:58, Alvaro Herrera wrote: > I don't find the proposed behavior all that suprising, which the > original behavior surely is. I guess the bigger question is whether the > values that timestamptz_part() returns for other cases (than epoch) > should also be different from 0 when an 'infinity' timestamp is passed. > (In other words, why should 0 be the assumed return value here?) > Well, for example, how do you go about answering the question "what is the day-of-month of the infinite timestamp?" The question is nonsense; it doesn't have a defined day of month, so I think we should be returning NULL or throwing an error. Returning zero is definitely wrong. I think throwing an error is the better way to go, as the user probably didn't intend to ask an incoherent question. It makes sense to special-case 'epoch' because it effectively converts the operation into interval math; if we ask "how many seconds from 1970-01-01 00:00 UTC until the infinite timestamp?" the answer is genuinely "infinite seconds". So +1 for the proposed change for epoch, and let's throw an error for the other date fields instead of returning zero. Cheers, BJ -- 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] [BUGS] extract(epoch from infinity) is not 0
Excerpts from Robert Haas's message of mié jul 13 16:13:12 -0400 2011: > On Jul 13, 2011, at 1:43 PM, Bruce Momjian wrote: > > Daniele Varrazzo wrote: > >> =# select extract(epoch from 'infinity'::timestamp); > >> date_part > >> --- > >> 0 > >> > >> A better value would be 'infinity'::float8. Ditto for -infinity. > > Looking at: > > > >timestamptz_part(PG_FUNCTION_ARGS) > > > > I see: > > > >if (TIMESTAMP_NOT_FINITE(timestamp)) > >{ > >result = 0; > >PG_RETURN_FLOAT8(result); > >} > > > > The assumption is that extracting _anything_ from an infinite timestamp > > should be zero, but I can see your point that epoch perhaps should be > > special-cased to return +/- inifinity. > It's sort of non-obvious that either behavior is better than the > other. We might just be replacing one surprising behavior with > another. I don't find the proposed behavior all that suprising, which the original behavior surely is. I guess the bigger question is whether the values that timestamptz_part() returns for other cases (than epoch) should also be different from 0 when an 'infinity' timestamp is passed. (In other words, why should 0 be the assumed return value here?) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] [BUGS] extract(epoch from infinity) is not 0
On Jul 13, 2011, at 1:43 PM, Bruce Momjian wrote: > Daniele Varrazzo wrote: >> Hello, >> >> =# select extract(epoch from 'infinity'::timestamp); >> date_part >> --- >> 0 >> >> A better value would be 'infinity'::float8. Ditto for -infinity. >> >> I'm trying to use a box-based index to represent the intervals in a >> table containing a pair of fields date_from, date_to (timestamps), >> where semi-open intervals are represented with +/- infinity. Building >> the boxes using extract(epoch from ...) creates wrong entries as >> semi-open intervals are converted into a box with a corner in (0,0). > > Looking at: > >timestamptz_part(PG_FUNCTION_ARGS) > > I see: > >if (TIMESTAMP_NOT_FINITE(timestamp)) >{ >result = 0; >PG_RETURN_FLOAT8(result); >} > > The assumption is that extracting _anything_ from an infinite timestamp > should be zero, but I can see your point that epoch perhaps should be > special-cased to return +/- inifinity. > > Does anyone object to changing this? It's sort of non-obvious that either behavior is better than the other. We might just be replacing one surprising behavior with another. ...Robert -- 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] [BUGS] extract(epoch from infinity) is not 0
Daniele Varrazzo wrote: > Hello, > > =# select extract(epoch from 'infinity'::timestamp); > date_part > --- > 0 > > A better value would be 'infinity'::float8. Ditto for -infinity. > > I'm trying to use a box-based index to represent the intervals in a > table containing a pair of fields date_from, date_to (timestamps), > where semi-open intervals are represented with +/- infinity. Building > the boxes using extract(epoch from ...) creates wrong entries as > semi-open intervals are converted into a box with a corner in (0,0). Looking at: timestamptz_part(PG_FUNCTION_ARGS) I see: if (TIMESTAMP_NOT_FINITE(timestamp)) { result = 0; PG_RETURN_FLOAT8(result); } The assumption is that extracting _anything_ from an infinite timestamp should be zero, but I can see your point that epoch perhaps should be special-cased to return +/- inifinity. Does anyone object to changing this? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers