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

From: Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Returning 'Infinity'::TIMESTAMPTZ from "to_timestamp" function
Date: 2015-11-09 05:15:16
Message-ID: CAKOSWNmE4kYcqFoyUuv-LUtL9M9gGeEppK4=73bg6-K7_g4tQQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2015-11-09 05:25:45 Re: Returning 'Infinity'::TIMESTAMPTZ from "to_timestamp" function
Previous Message 德哥 2015-11-09 05:00:30 can we add SKIP LOCKED to UPDATE?