From: | Phil Sorber <phil(at)omniti(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: converting between infinity timestamp and float8 (epoch) |
Date: | 2011-12-27 16:38:27 |
Message-ID: | CADAkt-jRYHGFjLb1NdU9CbNJvVPUbb2JQ+mTuxO+KegLDO3K+g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Tue, Dec 27, 2011 at 10:41 AM, Phil Sorber <phil(at)omniti(dot)com> wrote:
> 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?
My search foo failed me. Someone just pointed me to a similar
conversation from some months ago:
http://archives.postgresql.org/pgsql-hackers/2011-07/msg00677.php
I would propose that since we can't know the hour or minute of
infinity that we should return null for those. I think NaN would be
wrong because it is a real number, it's just unknown. If we can just
pass infinity through the function, I think we should.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-12-27 17:47:20 | Re: BUG #6360: with hold cursor, cause function executed twice and wrong results |
Previous Message | Phil Sorber | 2011-12-27 15:41:22 | converting between infinity timestamp and float8 (epoch) |