Thread: 'Infinity' in date columns?

'Infinity' in date columns?

From

"Ken Winter"

Date:

17 May 2010, 04:03:10

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:

17 May 2010, 04:18:26

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:

17 May 2010, 04:22:05

"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:

17 May 2010, 13:46:44

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:

17 May 2010, 15:45:33


> -----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