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

27.02.2016 09:57, Vitaly Burovoy:
> 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)

Hi,
thank you for the patches.
Could you explain, whether they depend on each other?

> 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").

You mention intervals here, and TODO item definitely says about
'infinity' interval,
while patch and all the following discussion concerns to timestamps.
Is it a typo or I misunderstood something important?
I assumed that following query will work, but it isn't. Could you
clarify that?
select to_timestamp('infinity'::interval);

> Proposed patch implements it.
>
> 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).

I think that you should update documentation. At least description of
epoch on this page:
http://www.postgresql.org/docs/devel/static/functions-datetime.html

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

(The |to_timestamp| function encapsulates the above conversion.)

More thoughts about the patch:

1. When I copy value from hints for min and max values (see examples
below), it works fine for min, while max still leads to error.
It comes from the check "if (seconds >= epoch_ubound)". I wonder,
whether you should change hint message?

select to_timestamp(-210866803200.000000);
to_timestamp
---------------------------------
4714-11-24 02:30:17+02:30:17 BC
(1 row)

select to_timestamp(9224318016000.000000);
ERROR: UNIX epoch out of range: "9224318016000.000000"
HINT: Maximal UNIX epoch value is "9224318016000.000000"

2. There is a comment about JULIAN_MAXYEAR inaccuracy in timestamp.h:

* IS_VALID_JULIAN checks the minimum date exactly, but is a bit sloppy
* about the maximum, since it's far enough out to not be especially
* interesting.

Maybe you can expand it?
- Is JULIAN_MAXYEAR4STAMPS helps to avoid overflow in all possible cases?
- Why do we need to hold both definitions? I suppose, it's a matter of
backward compatibility, isn't it?

3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice
abbreviation, but it seems slightly confusing to me.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company