Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group