converting between infinity timestamp and float8 (epoch)

From: Phil Sorber <phil(at)omniti(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: converting between infinity timestamp and float8 (epoch)
Date: 2011-12-27 15:41:22
Message-ID: CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

So first off some ground work:

postgres=# select 'infinity'::timestamp;
timestamp
-----------
infinity
(1 row)

postgres=# select 'infinity'::float8;
float8
----------
Infinity
(1 row)

Establishing that we do in fact have an infinity value for both the
timestamp type and the double precision type.

If I try to convert between them:

postgres=# select to_timestamp('infinity'::float8);
ERROR: timestamp out of range
CONTEXT: SQL function "to_timestamp" statement 1

Ok, so that didn't work. Maybe there is something in the SQL standard
stating that this should not be possible? At least it reports an
error.

However, if I try:

postgres=# select extract(epoch from 'infinity'::timestamp);
date_part
-----------
0
(1 row)

This seems busted. Even if we were to consider 0 to be a special
"error value" it would lead to things like this:

postgres=# select to_timestamp(extract(epoch from 'infinity'::timestamp));
to_timestamp
------------------------
1969-12-31 19:00:00-05
(1 row)

So I think the second form (extract) should return an error, or better
yet, they should both do the intuitive thing that is to return
'infinity' of the appropriate type.

Thoughts?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Phil Sorber 2011-12-27 16:38:27 Re: converting between infinity timestamp and float8 (epoch)
Previous Message wcting163 2011-12-27 10:11:06 BUG #6360: with hold cursor, cause function executed twice and wrong results