Thread: infinity as a date
I see that Postgres has a special keyword "infinity" for use with
timestamps. Is there an equivalent for dates?
It seems I can set a date field to be 'infinity'::timestamp, but I can't
find a way to then query a table for fields that are 'infinity' ...
I tried the following but it doesn't quite work:
JC=# create table test(a date);
CREATE TABLE
JC=# insert into test values('infinity'::timestamp);
INSERT 1030323 1
JC=# select * from test;
a
---
(1 row)
JC=# select * from test where a='infinity'::timestamp;
a
---
(0 rows)
Is there a correct way to represent a date that is "later/earlier than
any other date"?
Thanks!
Jc
On Wed, Dec 11, 2002 at 16:11:12 +0900, Jean-Christian Imbeault <jc@mega-bucks.co.jp> wrote: > I see that Postgres has a special keyword "infinity" for use with > timestamps. Is there an equivalent for dates? Dates cannot have a value of 'infinity'. Either you can store timestamps instead of dates or use another column to indicate to indicate the date is infinite and have your tests check that column as well as the date column.
Bruno Wolff III wrote:
>
> Dates cannot have a value of 'infinity'. Either you can store timestamps
> instead of dates or use another column to indicate to indicate the date
> is infinite and have your tests check that column as well as the date
> column.
Ok, so why does postgres let me get away with:
JC=# create table test(a date);
CREATE TABLE
JC=# insert into test values('infinity'::timestamp);
INSERT 1030323 1
If dates cannot hvae a value of infinity why can I insert 'infinity'
into a date field?
Should not postgres throw an error?
Jc
I've just executed that code and the single row in the table contains null,
not infinity.
insert into test values('infinity'::timestamp);
select * from test where a > now();
will return no rows.
Of course, this is no use if you want the field to be part of your primary
key.
----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, December 12, 2002 2:14 AM
Subject: Re: [GENERAL] infinity as a date
> Bruno Wolff III wrote:
> >
> > Dates cannot have a value of 'infinity'. Either you can store
timestamps
> > instead of dates or use another column to indicate to indicate the date
> > is infinite and have your tests check that column as well as the date
> > column.
>
> Ok, so why does postgres let me get away with:
>
> JC=# create table test(a date);
> CREATE TABLE
> JC=# insert into test values('infinity'::timestamp);
> INSERT 1030323 1
>
> If dates cannot hvae a value of infinity why can I insert 'infinity'
> into a date field?
>
> Should not postgres throw an error?
>
> Jc
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> I see that Postgres has a special keyword "infinity" for use with
> timestamps. Is there an equivalent for dates?
There is not.
> I tried the following but it doesn't quite work:
> JC=# create table test(a date);
> CREATE TABLE
> JC=# insert into test values('infinity'::timestamp);
> INSERT 1030323 1
> JC=# select * from test;
> a
> ---
> (1 row)
It appears that what actually gets stored in test.a is NULL ... which
is not surprising given the source code for timestamp_date():
if (TIMESTAMP_NOT_FINITE(timestamp))
PG_RETURN_NULL();
I think that this is an outright bug: if type DATE doesn't have a
concept of infinity then it should throw an error, not translate
infinity to NULL. NULL means "unknown", not "I cannot cope with this
value".
Comments?
regards, tom lane
Tom Lane wrote: > > I think that this is an outright bug: if type DATE doesn't have a > concept of infinity then it should throw an error, not translate > infinity to NULL. NULL means "unknown", not "I cannot cope with this > value". I agree, which is why I brought it up. As an aside, why is there a concept of an infinite timestamp but not one for date? I am puzzled as to why the there is no concept of an infinite date. Thanks! Jc
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> As an aside, why is there a concept of an infinite timestamp but not one
> for date?
Purely historical, I'd imagine. The various Postgres datatypes were
developed at different times by different people. Tom Lockhart perhaps
remembers more about this particular discrepancy.
If you are sufficiently annoyed, please submit patches to make DATE
treat MAXINT and MININT as +infinity and -infinity instead of normal
dates. I would expect we'd accept such a patch.
regards, tom lane
Tom Lane wrote: > > Purely historical, I'd imagine. Fair enough. > If you are sufficiently annoyed, please submit patches to make DATE > treat MAXINT and MININT as +infinity and -infinity instead of normal > dates. I would expect we'd accept such a patch. I am not annoyed, just curious. And I would love to submit patches but I am not up to it. My programming skills are only slightly better than that of a highly trained monkey ... But when I do feel proficient enough postgres is definitely the open-source project I would most like to contribute to. Jc
Tom and Everyone,
Not that I feel that I get a vote, but it seems to me that an infinite date
doesn't make any sense. An interval is a measure of something (a value),
which could be infinite, but a date is a point in time (not a value),
similar to a location, and I don't think that the concept of an infinite
point in time makes any more sense than an infinite street address.
Just my $0.02.
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Wednesday, December 11, 2002 10:35 PM
To: Jean-Christian Imbeault
Cc: pgsql-general
Subject: Re: [GENERAL] infinity as a date
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> As an aside, why is there a concept of an infinite timestamp but not one
> for date?
Purely historical, I'd imagine. The various Postgres datatypes were
developed at different times by different people. Tom Lockhart perhaps
remembers more about this particular discrepancy.
If you are sufficiently annoyed, please submit patches to make DATE
treat MAXINT and MININT as +infinity and -infinity instead of normal
dates. I would expect we'd accept such a patch.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Peter Darley wrote: > > Not that I feel that I get a vote, but it seems to me that an infinite date > doesn't make any sense. I agree, but if you are going to have infinite timestamps, you should have infinite dates. A timestamp is a point in time after all ... just like a date. I don't care either way, I just want consistency. Jc
Added to TODO:
* Allow infinite dates just like infinite timestamps
---------------------------------------------------------------------------
Jean-Christian Imbeault wrote:
> Peter Darley wrote:
> >
> > Not that I feel that I get a vote, but it seems to me that an infinite date
> > doesn't make any sense.
>
> I agree, but if you are going to have infinite timestamps, you should
> have infinite dates. A timestamp is a point in time after all ... just
> like a date.
>
> I don't care either way, I just want consistency.
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073