27.02.2016 09:57, Vitaly Burovoy:
I worked on a patch 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
... which is not good.
Supporting of such converting is in the TODO list (by "converting
between infinity timestamp and float8").
You mention intervals here, and TODO item definitely says about
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
Proposed patch implements it.
There is an other patch in the CF 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 (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?
4714-11-24 02:30:17+02:30:17 BC
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
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.
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company