Skip site navigation (1) Skip section navigation (2)

Re: converting between infinity timestamp and float8 (epoch)

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
Cc: Phil Sorber <phil(at)omniti(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,pgsql-bugs(at)postgresql(dot)org
Subject: Re: converting between infinity timestamp and float8 (epoch)
Date: 2012-02-05 04:39:25
Message-ID: 20120205043925.GB19450@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
TODO added:

	Determine how to represent date/time field extraction on infinite
	timestamps
	
	    extract(epoch from infinity) is not 0
	    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<tgl(at)sss(dot)pgh(dot)pa(dot)us>  wrote:
> >>Phil Sorber<phil(at)omniti(dot)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".  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 (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

In response to

pgsql-bugs by date

Next:From: Simon RiggsDate: 2012-02-05 20:42:47
Subject: Re: [BUGS] BUG #6425: Bus error in slot_deform_tuple
Previous:From: Mark PhillipsDate: 2012-02-04 22:19:09
Subject: Re: BUG #6404: postgres account not created during unattended install

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group