Re: Returning 'Infinity'::TIMESTAMPTZ from "to_timestamp" function

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)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 +