On 3/4/16, Anastasia Lubennikova <a.lubennik...@postgrespro.ru> wrote: > 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.
Thank you for the review. > Could you explain, whether they depend on each other? Only logically. They reverse each other: postgres=# SELECT v, to_timestamp(v), extract(epoch FROM to_timestamp(v)) FROM postgres-# unnest(ARRAY['+inf', '-inf', 0, 65536, 982384720.12]::float8[]) v; v | to_timestamp | date_part --------------+---------------------------+-------------- Infinity | infinity | Infinity -Infinity | -infinity | -Infinity 0 | 1970-01-01 00:00:00+00 | 0 65536 | 1970-01-01 18:12:16+00 | 65536 982384720.12 | 2001-02-17 04:38:40.12+00 | 982384720.12 (5 rows) >> 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, Yes, it is in the same block. But I wanted to point to the link "converting between infinity timestamp and float8". There are two-way conversion examples. > while patch and all the following discussion concerns to timestamps. > Is it a typo or I misunderstood something important? It is just a reason why I rewrote it as an internal function. I asked whether to just rewrite the function "pg_catalog.to_timestamp(float8)" as an internal one or to add support of infinite intervals. Tom Lane answered[5] "you should stay away from infinite intervals". So I left intervals as is. > I assumed that following query will work, but it isn't. Could you > clarify that? > select to_timestamp('infinity'::interval); It is not hard. There is no logical way to convert interval (e.g. "5minutes") to a timestamp (or date). There never was a function "to_timestamp(interval)" and never will be. postgres=# select to_timestamp('5min'::interval); ERROR: function to_timestamp(interval) does not exist LINE 1: select to_timestamp('1min'::interval); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. >> 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 Thank you very much for pointing where it is located (I saw only "to_timestamp(TEXT, TEXT)"). I'll think how to update it. > 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" I agree, it is a little confusing. Do you (or anyone) know how to construct a condensed phrase that it is an exclusive upper bound of an allowed UNIX epoch range? > 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. It is just about the accuracy to the day for a lower bound, and to the year (not to a day) for an upper bound. > 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? Yes. I tried to be less invasive from the point of view of endusers. They can be sure if they follow the documentation they won't get into trouble. > 3. (nitpicking) I don't sure about "4STAMPS" suffix. "4" is nice > abbreviation, but it seems slightly confusing to me. It doesn't matter for me what it is called, it is short enough and reflects a type on which it is applied. What would the best name be for it? [5]http://www.postgresql.org/message-id/21367.1447046...@sss.pgh.pa.us -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers