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
