Lists: | pgsql-bugspgsql-hackers |
---|
From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | extract(epoch from infinity) is not 0 |
Date: | 2011-07-12 11:37:41 |
Message-ID: | CA+mi_8bda-Fnev9iXeUbnqhVaCWzbYhHkWoxPQfBca9eDPpRMw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
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).
-- Daniele
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 18:43:08 |
Message-ID: | 201107131843.p6DIh8923252@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 20:13:12 |
Message-ID: | F5B7B9C4-66FD-4E47-9FBC-02EAB14B491E@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
On Jul 13, 2011, at 1:43 PM, Bruce Momjian <bruce(at)momjian(dot)us> 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
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 20:58:30 |
Message-ID: | 1310590466-sup-3055@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
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 <bruce(at)momjian(dot)us> 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 <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From: | Brendan Jurd <direvus(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 21:21:22 |
Message-ID: | CADxJZo0BXaetTCygQV+U-PCgtLv-ksBKTGiZbMzAJ5TzgUCsBg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
On 14 July 2011 06:58, Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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
From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 21:22:26 |
Message-ID: | 4E1E0C92.40205@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 21:22:36 |
Message-ID: | 10833.1310592156@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Jul 13, 2011, at 1:43 PM, Bruce Momjian <bruce(at)momjian(dot)us> 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
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Brendan Jurd <direvus(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 22:16:56 |
Message-ID: | 73A5666E-2D40-457E-9DFF-248895DB7FAF@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
On Jul 13, 2011, at 4:21 PM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> On 14 July 2011 06:58, Alvaro Herrera <alvherre(at)commandprompt(dot)com> 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
From: | Brendan Jurd <direvus(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [BUGS] extract(epoch from infinity) is not 0 |
Date: | 2011-07-13 22:44:03 |
Message-ID: | CADxJZo2NsoHaBAmufse1ZVtTRP=WBN7Jrx2qJnCG_H_KaokSpA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-hackers |
On 14 July 2011 08:16, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Jul 13, 2011, at 4:21 PM, Brendan Jurd <direvus(at)gmail(dot)com> 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