What is an "infinity" timestamp?

[fsalvelt]

Hello,

Can someone please explain what is the purpose of the "infinity" timestamp value?

Code: Select all

dbadmin=> select TIMESTAMP 'INFINITY';
 ?column?
----------
 infinity
(1 row)

Is it just an invalid date?

[JimKnicely]

Hi,

When dealing with timestamps, INFINITY is a placeholder value representing a date greater than all other dates while negative INFINITY is a placeholder value representing a date that is less than all other dates...

Example:

Code: Select all

dbadmin=> create table temp (col1 timestamp);
CREATE TABLE
dbadmin=> insert into temp values (TIMESTAMP 'infinity');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into temp values (TIMESTAMP '-infinity');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select col1 from temp;
   col1
-----------
 infinity
 -infinity
(2 rows)

In the following query we see that SYSDATE is less than INFINITY:

Code: Select all

dbadmin=> select col1 from temp where sysdate < col1;
   col1
----------
 infinity
(1 row)

And in the next query we see that negative INFINITY is less than SYSDATE:

Code: Select all

dbadmin=> select col1 from temp where col1 < sysdate;
   col1
-----------
 -infinity
(1 row)