Re: converting between infinity timestamp and float8 (epoch)
От | Phil Sorber |
---|---|
Тема | Re: converting between infinity timestamp and float8 (epoch) |
Дата | |
Msg-id | CADAkt-grBx8DGqWcKL1=514HrFXebLMoKmV0tmPWp+VPp=gF+Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: converting between infinity timestamp and float8 (epoch) (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: converting between infinity timestamp and float8 (epoch)
(Gavin Flower <GavinFlower@archidevsys.co.nz>)
|
Список | pgsql-bugs |
On Tue, Dec 27, 2011 at 1:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Phil Sorber <phil@omniti.com> 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= 5" LINE 1: select 'infinity-05'::timestamp with time zone; ^
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: converting between infinity timestamp and float8 (epoch)
Следующее
От: wcting163Дата:
Сообщение: with hold cursor, cause function execute twice and wrong result