Re: [HACKERS] Returning 'Infinity'::TIMESTAMPTZ from "to_timestamp" function

[Bruce Momjian]

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  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 +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Returning 'Infinity'::TIMESTAMPTZ from "to_timestamp" function

[Tom Lane]

Vitaly Burovoy  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers