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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Jul 13, 2011, at 1:43 PM, Bruce Momjian <bruce@momjian.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

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@momjian.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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 14 July 2011 06:58, Alvaro Herrera <alvherre@commandprompt.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

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

Robert Haas <robertmhaas@gmail.com> writes:

On Jul 13, 2011, at 1:43 PM, Bruce Momjian <bruce@momjian.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

On Jul 13, 2011, at 4:21 PM, Brendan Jurd <direvus@gmail.com> wrote:

On 14 July 2011 06:58, Alvaro Herrera <alvherre@commandprompt.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

On 14 July 2011 08:16, Robert Haas <robertmhaas@gmail.com> wrote:

On Jul 13, 2011, at 4:21 PM, Brendan Jurd <direvus@gmail.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