Thread: 'Infinity' in date columns?

'Infinity' in date columns?

From
"Ken Winter"
Date:

The documentation at http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to say that the special value ‘infinity’ (“later than all other time stamps”) should work for an date-time column, and the type “date” is listed as among the date-time data types.

 

But I can’t get ‘infinity’ to work for columns of type “date”.

 

Here’s a test case:

 

CREATE TABLE _test

(

  timestampx timestamp without time zone DEFAULT 'infinity'::timestamp without time zone,

  datex date DEFAULT 'infinity'::timestamp without time zone

)

);

 

INSERT INTO _test DEFAULT VALUES;

 

SELECT * FROM _test;

 

This returns (as viewed through pgAdmin III):

 

timestampx     datex

-----------     -----

infinity

 

When I tried to declared the “datex” column like this:

 

  datex date DEFAULT 'infinity'::date

 

I got this:

 

ERROR:  invalid input syntax for type date: "infinity"

 

Is there any way to get ‘infinity’ to work for a “date” column? 

 

Or any other way to get the equivalent functionality in a date column?

 

~ TIA

~ Ken

Re: 'Infinity' in date columns?

From
Shoaib Mir
Date:

On Mon, May 17, 2010 at 1:17 PM, Ken Winter <ken@sunward.org> wrote:

The documentation at http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to say that the special value ‘infinity’ (“later than all other time stamps”) should work for an date-time column, and the type “date” is listed as among the date-time data types.

 

But I can’t get ‘infinity’ to work for columns of type “date”.

 

I don't have version 8.3 with me right now but I just gave it a try with 8.4 and it gave me the expected output:

postgres=# CREATE TABLE _test
postgres-# (
postgres(#   timestampx timestamp without time zone DEFAULT 'infinity'::timestamp without time zone,
postgres(#   datex date DEFAULT 'infinity'::timestamp without time zone
postgres(# );
CREATE TABLE
postgres=# INSERT INTO _test DEFAULT VALUES;
INSERT 0 1
postgres=# SELECT * FROM _test;
 timestampx |  datex   
------------+----------
 infinity   | infinity
(1 row)

postgres=# select version();
                                             version                                              
--------------------------------------------------------------------------------------------------
 PostgreSQL 8.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 32-bit
(1 row)

 -- 
Shoaib Mir
http://shoaibmir.wordpress.com/

Re: 'Infinity' in date columns?

From
Tom Lane
Date:
"Ken Winter" <ken@sunward.org> writes:
> The documentation at
> http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems to
> say that the special value 'infinity' ("later than all other time stamps")
> should work for an date-time column, and the type "date" is listed as among
> the date-time data types.
> But I can't get 'infinity' to work for columns of type "date".

You seem to be carefully reading around the middle column in Table 8-13,
which specifically shows that infinity doesn't work for type date.

Now, if you were to update to Postgres 8.4, it *would* work.

            regards, tom lane

Re: 'Infinity' in date columns?

From
Lew
Date:
Ken Winter wrote:
>     The documentation at
>     http://www.postgresql.org/docs/8.3/static/datatype-datetime.html
>     seems to say that the special value ‘infinity’ (“later than all
>     other time stamps”) should work for an date-time column, and the
>     type “date” is listed as among the date-time data types.
>
>     But I can’t get ‘infinity’ to work for columns of type “date”.

Shoaib Mir wrote:
> I don't have version 8.3 with me right now but I just gave it a try with
> 8.4 and it gave me the expected output:

As Tom Lane points out, that's a difference between 8.3 and 8.4.

--
Lew

Re: 'Infinity' in date columns?

From
"Ken Winter"
Date:

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tom Lane
...
>
> "Ken Winter" <ken@sunward.org> writes:
> > The documentation at
> > http://www.postgresql.org/docs/8.3/static/datatype-datetime.html seems
> to
> > say that the special value 'infinity' ("later than all other time
> stamps")
> > should work for an date-time column, and the type "date" is listed as
> among
> > the date-time data types.
> > But I can't get 'infinity' to work for columns of type "date".
>
> You seem to be carefully reading around the middle column in Table 8-13,
> which specifically shows that infinity doesn't work for type date.

Oh, duh, right you are...

> Now, if you were to update to Postgres 8.4, it *would* work.

I'll see if I can get my host to do that.

~ Thanks
~ Ken