Обсуждение: 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 +