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)

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:

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);
 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

Reply via email to