Re: [PATCH] Supporting +-Infinity values by to_timestamp(float8)

27.02.2016 09:57, Vitaly Burovoy:<br /><blockquote
cite="mid:CAKOSWN=qbbnF_oRue4rfsMmWb+7wkBN6fq5XNHgt5vR0TiAA5g@mail.gmail.com"type="cite"><pre wrap="">Hello, Hackers!
 

I worked on a patch[1] allows "EXTRACT(epoch FROM
+-Inf::timestamp[tz])" to return "+-Inf::float8".
There is an opposite function "to_timestamp(float8)" which now defined as:
SELECT ('epoch'::timestamptz + $1 * '1 second'::interval)
</pre></blockquote><br /> Hi, <br /> thank you for the patches.<br /> Could you explain, whether they depend on each
other?<br/><br /><blockquote cite="mid:CAKOSWN=qbbnF_oRue4rfsMmWb+7wkBN6fq5XNHgt5vR0TiAA5g@mail.gmail.com"
type="cite"><prewrap="">Since intervals do not support infinity values, it is impossible to do
 
something like:

SELECT to_timestamp('infinity'::float8);

... which is not good.

Supporting of such converting is in the TODO list[2] (by "converting
between infinity timestamp and float8").
</pre></blockquote><br /> You mention intervals here, and TODO item definitely says about 'infinity' interval,<br />
whilepatch and all the following discussion concerns to timestamps.<br /> Is it a typo or I misunderstood something
important?<br /> I assumed that following query will work, but it isn't. Could you clarify that?<br /> select
to_timestamp('infinity'::interval);<br/><br /><blockquote
cite="mid:CAKOSWN=qbbnF_oRue4rfsMmWb+7wkBN6fq5XNHgt5vR0TiAA5g@mail.gmail.com"type="cite"><pre wrap="">Proposed patch
implementsit.
 

There is an other patch in the CF[3] 2016-03 implements checking of
timestamp[tz] for being in allowed range. Since it is wise to set
(fix) the upper boundary of timestamp[tz]s, I've included the file
"src/include/datatype/timestamp.h" from there to check that an input
value and a result are in the allowed range.

There is no changes in a documentation because allowed range is the
same as officially supported[4] (i.e. until 294277 AD).
</pre></blockquote><br /> I think that you should update documentation. At least description of epoch on this page:<br
/><ahref="http://www.postgresql.org/docs/devel/static/functions-datetime.html"><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/devel/static/functions-datetime.html">http://www.postgresql.org/docs/devel/static/functions-datetime.html</a></a><br
/><p>Hereis how you can convert an epoch value back to a time stamp:<pre class="SCREEN">SELECT TIMESTAMP WITH TIME ZONE
'epoch'+ 982384720.12 * INTERVAL '1 second';
 
</pre><p>(The <code class="FUNCTION">to_timestamp</code> function encapsulates the above conversion.)<br /> More
thoughtsabout the patch:<br /><br /> 1. When I copy value from hints for min and max values (see examples below), it
worksfine for min, while max still leads to error.<br /> It comes from the check   "if (seconds >= epoch_ubound)". I
wonder,whether you should change hint message? <br /><br /> select to_timestamp(-210866803200.000000);<br />          
to_timestamp          <br /> ---------------------------------<br />  4714-11-24 02:30:17+02:30:17 BC<br /> (1 row)<br
/><br/><br /> select to_timestamp(9224318016000.000000);<br /> ERROR:  UNIX epoch out of range:
"9224318016000.000000"<br/> HINT:  Maximal UNIX epoch value is "9224318016000.000000"<br /><br /> 2. There is a comment
aboutJULIAN_MAXYEAR inaccuracy in timestamp.h:<br /><br />  * IS_VALID_JULIAN checks the minimum date exactly, but is a
bitsloppy<br />  * about the maximum, since it's far enough out to not be especially<br />  * interesting.<br /><br />
Maybeyou can expand it?<br /> - Is JULIAN_MAXYEAR4STAMPS helps to avoid overflow in all possible cases?<br /> - Why do
weneed to hold both definitions? I suppose, it's a matter of backward compatibility, isn't it?<br /><br /> 3.
(nitpicking)I don't sure about "4STAMPS" suffix. "4" is nice abbreviation, but it seems slightly confusing to me.<br
/><preclass="moz-signature" cols="72">-- 
 
Anastasia Lubennikova
Postgres Professional: <a class="moz-txt-link-freetext"
href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company</pre>