converting between infinity timestamp and float8 (epoch)

Поиск
Список
Период
Сортировка
От Phil Sorber
Тема converting between infinity timestamp and float8 (epoch)
Дата
Msg-id CADAkt-icuESH16uLOCXbR-dKpcvwtUJE4JWXnkdAjAAwP6j12g@mail.gmail.com
обсуждение исходный текст
Ответы Re: converting between infinity timestamp and float8 (epoch)  (Phil Sorber <phil@omniti.com>)
Список 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?

В списке pgsql-bugs по дате отправления:

Предыдущее
От: wcting163@163.com
Дата:
Сообщение: BUG #6360: with hold cursor, cause function executed twice and wrong results
Следующее
От: Phil Sorber
Дата:
Сообщение: Re: converting between infinity timestamp and float8 (epoch)