Establishing that we do in fact have an infinity value for both the
timestamp type and the double precision type.
If I try to convert between them:
postgres=# select to_timestamp('infinity'::float8);
ERROR: timestamp out of range
CONTEXT: SQL function "to_timestamp" statement 1
Ok, so that didn't work. Maybe there is something in the SQL standard
stating that this should not be possible? At least it reports an
error.
However, if I try:
postgres=# select extract(epoch from 'infinity'::timestamp);
date_part
-----------
(1 row)
This seems busted. Even if we were to consider 0 to be a special
"error value" it would lead to things like this:
postgres=# select to_timestamp(extract(epoch from 'infinity'::timestamp));
to_timestamp
------------------------
1969-12-31 19:00:00-05
(1 row)
So I think the second form (extract) should return an error, or better
yet, they should both do the intuitive thing that is to return
'infinity' of the appropriate type.
Thoughts?
On Tue, Dec 27, 2011 at 10:41 AM, Phil Sorber <[email protected]> wrote:
> So first off some ground work:
> postgres=3D# select 'infinity'::timestamp;
> =A0timestamp
> -----------
> =A0infinity
> (1 row)
> postgres=3D# select 'infinity'::float8;
> =A0float8
> ----------
> =A0Infinity
> (1 row)
> Establishing that we do in fact have an infinity value for both the
> timestamp type and the double precision type.
> If I try to convert between them:
> postgres=3D# select to_timestamp('infinity'::float8);
> ERROR: =A0timestamp out of range
> CONTEXT: =A0SQL function "to_timestamp" statement 1
> Ok, so that didn't work. Maybe there is something in the SQL standard
> stating that this should not be possible? At least it reports an
> error.
> However, if I try:
> postgres=3D# select extract(epoch from 'infinity'::timestamp);
> =A0date_part
> -----------
> =A0 =A0 =A0 =A0 0
> (1 row)
> This seems busted. Even if we were to consider 0 to be a special
> "error value" it would lead to things like this:
> postgres=3D# select to_timestamp(extract(epoch from 'infinity'::timestamp=
> =A0 =A0 =A0to_timestamp
> ------------------------
> =A01969-12-31 19:00:00-05
> (1 row)
> So I think the second form (extract) should return an error, or better
> yet, they should both do the intuitive thing that is to return
> 'infinity' of the appropriate type.
> Thoughts?
My search foo failed me. Someone just pointed me to a similar
conversation from some months ago:
http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php
I would propose that since we can't know the hour or minute of
infinity that we should return null for those. I think NaN would be
wrong because it is a real number, it's just unknown. If we can just
pass infinity through the function, I think we should.
Phil Sorber <[email protected]> writes:
> My search foo failed me. Someone just pointed me to a similar
> conversation from some months ago:
> http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php
> I would propose that since we can't know the hour or minute of
> infinity that we should return null for those. I think NaN would be
> wrong because it is a real number, it's just unknown. If we can just
> pass infinity through the function, I think we should.
The last thread ended with a request for somebody to think through
the behavior for *all* extract field types and make a coherent proposal.
I don't think you've really advanced the discussion yet.
I think I agree with the position that we shouldn't return 0 unless
the correct value actually is 0, but it's not clear to me whether
to use NULL or NaN to represent "indeterminate". Traditionally we
consider NULL to mean "unknown", but it seems like "what's the hour
of an infinite timestamp" is a subtly different sort of situation:
it's not unknown, we know perfectly well that it's indeterminate.
OTOH, choosing NaN would put a pretty significant dependence on
IEEE-float arithmetic into the external specification of timestamps,
and I find that a bit worrisome, even though IEEE float arithmetic
is nigh universal these days. So maybe splitting hairs like that
would be ill-advised. It probably depends also on what you expect
people to do with the result of extract() --- NULL would presumably
propagate through any additional calculation steps as-is, whereas
NaN might have less predictable behavior.
There was also some support for throwing an error in the previous
thread, though I can't say I like that answer myself.
regards, tom lane
On Tue, Dec 27, 2011 at 1:00 PM, Tom Lane <[email protected]> wrote:
> Phil Sorber <[email protected]> writes:
>> My search foo failed me. Someone just pointed me to a similar
>> conversation from some months ago:
>> http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php
>> I would propose that since we can't know the hour or minute of
>> infinity that we should return null for those. I think NaN would be
>> wrong because it is a real number, it's just unknown. If we can just
>> pass infinity through the function, I think we should.
> The last thread ended with a request for somebody to think through
> the behavior for *all* extract field types and make a coherent proposal.
> I don't think you've really advanced the discussion yet.
> I think I agree with the position that we shouldn't return 0 unless
> the correct value actually is 0, but it's not clear to me whether
> to use NULL or NaN to represent "indeterminate". =A0Traditionally we
> consider NULL to mean "unknown", but it seems like "what's the hour
> of an infinite timestamp" is a subtly different sort of situation:
> it's not unknown, we know perfectly well that it's indeterminate.
> OTOH, choosing NaN would put a pretty significant dependence on
> IEEE-float arithmetic into the external specification of timestamps,
> and I find that a bit worrisome, even though IEEE float arithmetic
> is nigh universal these days. =A0So maybe splitting hairs like that
> would be ill-advised. =A0It probably depends also on what you expect
> people to do with the result of extract() --- NULL would presumably
> propagate through any additional calculation steps as-is, whereas
> NaN might have less predictable behavior.
> There was also some support for throwing an error in the previous
> thread, though I can't say I like that answer myself.
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
It is my understanding that NULL would be for "unknown" or "undefined"
and NaN for "indeterminate" as well as some other cases like complex
numbers. I believe per the standard NaN explicitly includes
indeterminate forms. But I don't think extract(hour from
'infinity'::timestamp) is an indeterminate form
(http://en.wikipedia.org/wiki/Indeterminate_form). It is an
oscillating function similar to sin(x). Limit of sin(x) as x
approaches infinity is undefined. To me that points to NULL as the
appropriate value.
Also, like epoch, the expressions that involve year are not
oscillating. They are monotonic. the limit of extract(millennium from
'infinity'::timestamp) is infinity.
I'm not going to claim to be a mathematician, so I concede I might be
wrong with my thought process here.
Given the preceding is true, my proposal is the following for
extract() when passed an infinite timestamp:
1) Monotonic values (century, decade, epoch, isoyear, millennium and
year) we return 'infinity'::float8 signed appropriately.
2) Oscillating values (day, dow, doy, hour, isodow, microseconds,
milliseconds, minute, month, quarter, second and week) would return
NULL.
3) timezone, timezone_hour and timezone_minute are almost a separate
issue since timezone is separate from the value. So we should support
something like 'infinity-05'::timestamp with time zone. Then the
timezone stuff would just behave normally.
Currently it does this:
postgres=3D# select 'infinity+00'::timestamp with time zone;
timestamptz
-------------
infinity
(1 row)
postgres=3D# select 'infinity-05'::timestamp with time zone;
ERROR: invalid input syntax for type timestamp with time zone: "infinity-0=
LINE 1: select 'infinity-05'::timestamp with time zone;
On 28/12/11 10:43, Phil Sorber wrote:
> On Tue, Dec 27, 2011 at 1:00 PM, Tom Lane<[email protected]> wrote:
>> Phil Sorber<[email protected]> writes:
>>> My search foo failed me. Someone just pointed me to a similar
>>> conversation from some months ago:
>>> http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php
>>> I would propose that since we can't know the hour or minute of
>>> infinity that we should return null for those. I think NaN would be
>>> wrong because it is a real number, it's just unknown. If we can just
>>> pass infinity through the function, I think we should.
>> The last thread ended with a request for somebody to think through
>> the behavior for *all* extract field types and make a coherent proposal.
>> I don't think you've really advanced the discussion yet.
>> I think I agree with the position that we shouldn't return 0 unless
>> the correct value actually is 0, but it's not clear to me whether
>> to use NULL or NaN to represent "indeterminate". Traditionally we
>> consider NULL to mean "unknown", but it seems like "what's the hour
>> of an infinite timestamp" is a subtly different sort of situation:
>> it's not unknown, we know perfectly well that it's indeterminate.
>> OTOH, choosing NaN would put a pretty significant dependence on
>> IEEE-float arithmetic into the external specification of timestamps,
>> and I find that a bit worrisome, even though IEEE float arithmetic
>> is nigh universal these days. So maybe splitting hairs like that
>> would be ill-advised. It probably depends also on what you expect
>> people to do with the result of extract() --- NULL would presumably
>> propagate through any additional calculation steps as-is, whereas
>> NaN might have less predictable behavior.
>> There was also some support for throwing an error in the previous
>> thread, though I can't say I like that answer myself.
>> regards, tom lane
> It is my understanding that NULL would be for "unknown" or "undefined"
> and NaN for "indeterminate" as well as some other cases like complex
> numbers. I believe per the standard NaN explicitly includes
> indeterminate forms. But I don't think extract(hour from
> 'infinity'::timestamp) is an indeterminate form
> (http://en.wikipedia.org/wiki/Indeterminate_form). It is an
> oscillating function similar to sin(x). Limit of sin(x) as x
> approaches infinity is undefined. To me that points to NULL as the
> appropriate value.
> Also, like epoch, the expressions that involve year are not
> oscillating. They are monotonic. the limit of extract(millennium from
> 'infinity'::timestamp) is infinity.
> I'm not going to claim to be a mathematician, so I concede I might be
> wrong with my thought process here.
> Given the preceding is true, my proposal is the following for
> extract() when passed an infinite timestamp:
> 1) Monotonic values (century, decade, epoch, isoyear, millennium and
> year) we return 'infinity'::float8 signed appropriately.
> 2) Oscillating values (day, dow, doy, hour, isodow, microseconds,
> milliseconds, minute, month, quarter, second and week) would return
> NULL.
> 3) timezone, timezone_hour and timezone_minute are almost a separate
> issue since timezone is separate from the value. So we should support
> something like 'infinity-05'::timestamp with time zone. Then the
> timezone stuff would just behave normally.
> Currently it does this:
> postgres=# select 'infinity+00'::timestamp with time zone;
> timestamptz
> -------------
> infinity
> (1 row)
> postgres=# select 'infinity-05'::timestamp with time zone;
> ERROR: invalid input syntax for type timestamp with time zone: "infinity-05"
> LINE 1: select 'infinity-05'::timestamp with time zone;
Hmm...
Infinity is conceptually the 'maximum' value possible - or more
pr4ecisely: a value greater than any you can specify a concrete value
for in finite time.
So I think the appropriate value should be the maximum representational
possibility and should be the same regardless of time zone, plus any
operation such as adding or subtracting finite values should not change
it (arithmetic ops with another 'infinite' value should be either an
error or a NaN/Null). This is to consistent that with the notion of
infinity.
I would suggest that hh:mm:ss.ssss...
should be: 23:59:59.9999...
Cheers,
Gavin
converting between infinity timestamp and float8
---------------------------------------------------------------------------
On Fri, Dec 30, 2011 at 12:51:56PM +1300, Gavin Flower wrote:
> On 28/12/11 10:43, Phil Sorber wrote:
> >On Tue, Dec 27, 2011 at 1:00 PM, Tom Lane<[email protected]> wrote:
> >>Phil Sorber<[email protected]> writes:
> >>>My search foo failed me. Someone just pointed me to a similar
> >>>conversation from some months ago:
> >>>http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php
> >>>I would propose that since we can't know the hour or minute of
> >>>infinity that we should return null for those. I think NaN would be
> >>>wrong because it is a real number, it's just unknown. If we can just
> >>>pass infinity through the function, I think we should.
> >>The last thread ended with a request for somebody to think through
> >>the behavior for *all* extract field types and make a coherent proposal.
> >>I don't think you've really advanced the discussion yet.
> >>I think I agree with the position that we shouldn't return 0 unless
> >>the correct value actually is 0, but it's not clear to me whether
> >>to use NULL or NaN to represent "indeterminate". Traditionally we
> >>consider NULL to mean "unknown", but it seems like "what's the hour
> >>of an infinite timestamp" is a subtly different sort of situation:
> >>it's not unknown, we know perfectly well that it's indeterminate.
> >>OTOH, choosing NaN would put a pretty significant dependence on
> >>IEEE-float arithmetic into the external specification of timestamps,
> >>and I find that a bit worrisome, even though IEEE float arithmetic
> >>is nigh universal these days. So maybe splitting hairs like that
> >>would be ill-advised. It probably depends also on what you expect
> >>people to do with the result of extract() --- NULL would presumably
> >>propagate through any additional calculation steps as-is, whereas
> >>NaN might have less predictable behavior.
> >>There was also some support for throwing an error in the previous
> >>thread, though I can't say I like that answer myself.
> >> regards, tom lane
> >It is my understanding that NULL would be for "unknown" or "undefined"
> >and NaN for "indeterminate" as well as some other cases like complex
> >numbers. I believe per the standard NaN explicitly includes
> >indeterminate forms. But I don't think extract(hour from
> >'infinity'::timestamp) is an indeterminate form
> >(http://en.wikipedia.org/wiki/Indeterminate_form). It is an
> >oscillating function similar to sin(x). Limit of sin(x) as x
> >approaches infinity is undefined. To me that points to NULL as the
> >appropriate value.
> >Also, like epoch, the expressions that involve year are not
> >oscillating. They are monotonic. the limit of extract(millennium from
> >'infinity'::timestamp) is infinity.
> >I'm not going to claim to be a mathematician, so I concede I might be
> >wrong with my thought process here.
> >Given the preceding is true, my proposal is the following for
> >extract() when passed an infinite timestamp:
> >1) Monotonic values (century, decade, epoch, isoyear, millennium and
> >year) we return 'infinity'::float8 signed appropriately.
> >2) Oscillating values (day, dow, doy, hour, isodow, microseconds,
> >milliseconds, minute, month, quarter, second and week) would return
> >NULL.
> >3) timezone, timezone_hour and timezone_minute are almost a separate
> >issue since timezone is separate from the value. So we should support
> >something like 'infinity-05'::timestamp with time zone. Then the
> >timezone stuff would just behave normally.
> >Currently it does this:
> >postgres=# select 'infinity+00'::timestamp with time zone;
> > timestamptz
> >-------------
> > infinity
> >(1 row)
> >postgres=# select 'infinity-05'::timestamp with time zone;
> >ERROR: invalid input syntax for type timestamp with time zone: "infinity-05"
> >LINE 1: select 'infinity-05'::timestamp with time zone;
> Hmm...
> Infinity is conceptually the 'maximum' value possible - or more
> pr4ecisely: a value greater than any you can specify a concrete
> value for in finite time.
> So I think the appropriate value should be the maximum
> representational possibility and should be the same regardless of
> time zone, plus any operation such as adding or subtracting finite
> values should not change it (arithmetic ops with another 'infinite'
> value should be either an error or a NaN/Null). This is to
> consistent that with the notion of infinity.
> I would suggest that hh:mm:ss.ssss...
> should be: 23:59:59.9999...
> Cheers,
> Gavin
> Sent via pgsql-bugs mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
By continuing to browse this website, you agree to the use of cookies. Go to Privacy Policy.
