Thread: Returning 'Infinity'::TIMESTAMPTZ from "to_timestamp" function
Hello everyone! Continuing the topic of extracting EPOCH from 'Infinity'::TIMESTAMPTZ and according to an item "converting between infinity timestamp and float8" in the TODO list... Even when "SELECT extract(EPOCH FROM TIMESTAMPTZ 'Infinity')" results 'Infinity'::float, there is still trouble to convert it back: # SELECT to_timestamp('Infinity'::float); ERROR: timestamp out of range CONTEXT: SQL function "to_timestamp" statement 1 The function "to_timestamp(double precision)" is defined as an SQL-script: select ('epoch'::pg_catalog.timestamptz + $1 * '1 second'::pg_catalog.interval) Whereas error message points to a function "timestamptz_pl_interval", there is still a nuance in a function "interval_mul", because it returns "Interval->time" as "-Infinity" for both +/-infinity as an input value (apart from the fact that INTERVAL does not support infinite values officially). To add an ability to construct 'Infinity' TIMESTAMPTZ via "to_timestamp" call, there are two ways: 1. Rewrite the function "pg_catalog.to_timestamp(double precision)" as an internal one. It's the easiest way, because it allows to avoid usage of INTERVAL as a helper (of course, there is still possible to use intervals as shown above in user's scripts, but without "Infinity" support). 2. Add support of infinite intervals. It is harder, because it touches a lot of functions. I can add that support if it is in demand. Which way is preferred? -- Best regards, Vitaly Burovoy
Vitaly Burovoy <vitaly.burovoy@gmail.com> writes: > To add an ability to construct 'Infinity' TIMESTAMPTZ via > "to_timestamp" call, there are two ways: > 1. Rewrite the function "pg_catalog.to_timestamp(double precision)" as > an internal one. It's the easiest way, because it allows to avoid > usage of INTERVAL as a helper (of course, there is still possible to > use intervals as shown above in user's scripts, but without "Infinity" > support). > 2. Add support of infinite intervals. It is harder, because it touches > a lot of functions. I can add that support if it is in demand. > Which way is preferred? I think you should stay away from infinite intervals; that seems like there would be a lot of definitional questions to be resolved. Even if we decide we want to deal with that someday, it shouldn't be a blocking issue for conversion between infinite floats and infinite timestamps. regards, tom lane
FYI, I show this as fixed in 9.6: test=> SELECT to_timestamp('Infinity'::float); to_timestamp-------------- infinity(1 row) --------------------------------------------------------------------------- On Sun, Nov 8, 2015 at 09:15:16PM -0800, Vitaly Burovoy wrote: > Hello everyone! > > Continuing the topic of extracting EPOCH from 'Infinity'::TIMESTAMPTZ > and according to an item "converting between infinity timestamp and > float8" in the TODO list... > > Even when "SELECT extract(EPOCH FROM TIMESTAMPTZ 'Infinity')" results > 'Infinity'::float, there is still trouble to convert it back: > # SELECT to_timestamp('Infinity'::float); > ERROR: timestamp out of range > CONTEXT: SQL function "to_timestamp" statement 1 > > The function "to_timestamp(double precision)" is defined as an SQL-script: > select ('epoch'::pg_catalog.timestamptz + $1 * '1 second'::pg_catalog.interval) > > Whereas error message points to a function "timestamptz_pl_interval", > there is still a nuance in a function "interval_mul", because it > returns "Interval->time" as "-Infinity" for both +/-infinity as an > input value (apart from the fact that INTERVAL does not support > infinite values officially). > > To add an ability to construct 'Infinity' TIMESTAMPTZ via > "to_timestamp" call, there are two ways: > > 1. Rewrite the function "pg_catalog.to_timestamp(double precision)" as > an internal one. It's the easiest way, because it allows to avoid > usage of INTERVAL as a helper (of course, there is still possible to > use intervals as shown above in user's scripts, but without "Infinity" > support). > > 2. Add support of infinite intervals. It is harder, because it touches > a lot of functions. I can add that support if it is in demand. > > Which way is preferred? > -- > 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 -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +