Thread: Why overlaps is not working

set datestyle to iso,iso;

select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
   ('2006-10-16'::DATE, '2006-10-31':: DATE)


does not return any rows.

Why ?
How to make overlaps to return correct result?

Andrus.

Hm, why not this one:

select ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE);

 overlaps
----------
 f
(1 row)

Greetings,
Matthias

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrus
> Sent: Thursday, November 09, 2006 2:47 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Why overlaps is not working
>
>
> set datestyle to iso,iso;
>
> select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
>    ('2006-10-16'::DATE, '2006-10-31':: DATE)
>
>
> does not return any rows.
>
> Why ?
> How to make overlaps to return correct result?
>
> Andrus.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
               http://www.postgresql.org/docs/faq

Andrus wrote:
> set datestyle to iso,iso;
>
> select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
>    ('2006-10-16'::DATE, '2006-10-31':: DATE)
>
>
> does not return any rows.
>
> Why ?

They're adjacent, they don't overlap. Check the documentation on
OVERLAPS, I'm sure it's explicit about whether it is inclusive or
exclusive (the latter apparently).

> How to make overlaps to return correct result?

  select 1 where ('2006-10-30'::date, '9999-12-31'::date) OVERLAPS
     ('2006-10-16'::DATE, '2006-10-31':: DATE);
  ?column?
----------
         1
(1 row)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

am  Thu, dem 09.11.2006, um 15:46:50 +0200 mailte Andrus folgendes:
> set datestyle to iso,iso;
>
> select 1 where ('2006-10-31'::date, '9999-12-31'::date) OVERLAPS
>    ('2006-10-16'::DATE, '2006-10-31':: DATE)
>
>
> does not return any rows.
>
> Why ?
> How to make overlaps to return correct result?

Because they don't overlaps.

Example:

test=*# select ('2006-10-01'::date, '2006-10-11'::date) OVERLAPS ('2006-10-11'::DATE, '2006-10-20'::DATE);
 overlaps
----------
 f
(1 row)

test=*# select ('2006-10-01'::date, '2006-10-12'::date) OVERLAPS ('2006-10-11'::DATE, '2006-10-20'::DATE);
 overlaps
----------
 t
(1 row)


Your date-range don't overlap, because the 2nd ends '2006-10-31' and the other
begin with '2006-10-31'. And your query can't return anything because the where-condition
returns false.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

I have a number of select statements (in 8.1 and 8.2beta) which assume that
overlaps returns true for those cases.

Which the best way to fix them ?

Should I use AND, OR and date comparison operators instead of OVERLAPS ?

Andrus.

> They're adjacent, they don't overlap. Check the documentation on OVERLAPS,
> I'm sure it's explicit about whether it is inclusive or exclusive (the
> latter apparently).

8.2 doc does not explain term overlap. It only says:

"This expression yields true when two time periods (defined by their
endpoints) overlap"

>> How to make overlaps to return correct result?
>
>  select 1 where ('2006-10-30'::date, '9999-12-31'::date) OVERLAPS
>     ('2006-10-16'::DATE, '2006-10-31':: DATE);

In real queries I have column names and parameters instead of data
constants.
The only way it seems to replace OVERLAPS operator with AND, OR, <=
operators.

Is it so ?

Andrus.

Alban Hertroys <alban@magproductions.nl> writes:
> They're adjacent, they don't overlap. Check the documentation on
> OVERLAPS, I'm sure it's explicit about whether it is inclusive or
> exclusive (the latter apparently).

It's not very clear, but the spec defines (S1,T1) OVERLAPS (S2,T2)
as

              ( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
              OR
              ( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
              OR
              ( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )

(for the simple case where there are no nulls and S1 <= T1, S2 <= T2).
So it looks to me like the intervals are actually considered to be
half-open intervals [S1, T1).  Which is something that has its uses,
but it's a bit surprising compared to, say, BETWEEN.

If you don't like it, write your own comparison function ...

            regards, tom lane

Andrus wrote:
> I have a number of select statements (in 8.1 and 8.2beta) which assume that
> overlaps returns true for those cases.
>
> Which the best way to fix them ?
>
> Should I use AND, OR and date comparison operators instead of OVERLAPS ?

Why not just subtract/add 1, so that the check includes the boundary dates?

Like so;
  select 1 where ('2006-10-31'::date -1, '9999-12-31'::date +1) OVERLAPS
    ('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

> Why not just subtract/add 1, so that the check includes the boundary
> dates?
>
> Like so;
>  select 1 where ('2006-10-31'::date -1, '9999-12-31'::date +1) OVERLAPS
>    ('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)

Alban,

thank you. I use only dates as OVERLAPS arguments.
I changed all my WHERE clauses from

WHERE (a,b) OVERLAPS (c,d)

to

WHERE (a-1,b+1) OVERLAPS (c-1,d+1)

Will this give correct results ?

Andrus.

"Andrus" <eetasoft@online.ee> writes:

>> Why not just subtract/add 1, so that the check includes the boundary
>> dates?
>>
>> Like so;
>>  select 1 where ('2006-10-31'::date -1, '9999-12-31'::date +1) OVERLAPS
>>    ('2006-10-16'::DATE -1, '2006-10-31':: DATE +1)
>
> Alban,
>
> thank you. I use only dates as OVERLAPS arguments.
> I changed all my WHERE clauses from
>
> WHERE (a,b) OVERLAPS (c,d)
>
> to
>
> WHERE (a-1,b+1) OVERLAPS (c-1,d+1)
>
> Will this give correct results ?

It might give you false positives...

2006-11-30 -- 2006-12-05   AND    2006-12-06 -- 2006-12-15  (original) -- FALSE
2006-11-29 -- 2006-12-06   AND    2006-12-05 -- 2006-12-16  (changed)  -- TRUE


Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

>> WHERE (a,b) OVERLAPS (c,d)
>>
>> to
>>
>> WHERE (a-1,b+1) OVERLAPS (c-1,d+1)
>>
>> Will this give correct results ?
>
> It might give you false positives...
>
> 2006-11-30 -- 2006-12-05   AND    2006-12-06 -- 2006-12-15  (original) --
> FALSE
> 2006-11-29 -- 2006-12-06   AND    2006-12-05 -- 2006-12-16  (changed)  --
> TRUE

Jorge,

Thank you very much. Now I try to William Leite Ara�jo solution by replacing

WHERE (a,b) OVERLAPS (c,d)

with

WHERE  ( c BETWEEN a AND b ) OR  ( d BETWEEN a AND b )

Is this OK ?
This requires writing a and b expressions twice. How to avoid repeating
expressions ?

Andrus.

"Andrus" <eetasoft@online.ee> writes:

> Jorge,
>
> Thank you very much. Now I try to William Leite Araújo solution by replacing
>
> WHERE (a,b) OVERLAPS (c,d)
>
> with
>
> WHERE  ( c BETWEEN a AND b ) OR  ( d BETWEEN a AND b )
>
> Is this OK ?

From bare tests this looks OK.

> This requires writing a and b expressions twice. How to avoid repeating
> expressions ?

You can use a function for that and use variables for the four arguments:

CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
       date, date, date, out overlaps bool) as
$_$
SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
$_$ language sql;


Be seeing you,
--
Jorge Godoy      <jgodoy@gmail.com>

> CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
>       date, date, date, out overlaps bool) as
> $_$
> SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
> $_$ language sql;

Thank you.
In my application second and fourth parameters can be NULL which means
forever.
So I tried the code:

CREATE OR REPLACE FUNCTION public.doverlaps(date,
        date, date, date, out bool) IMMUTABLE AS
$_$
IF $1 is NULL OR $3 IS NULL THEN
  RAISE EXCEPTION 'doverlaps: first or third parameter is NULL % %',$1,$3;
  END IF;

IF $2 is null and $4 is null THEN
  SELECT true;
  RETURN;
END IF;

IF $2 is null THEN
  SELECT $1<=$4;
  RETURN;
END IF;

IF $4 is null THEN
  SELECT $2>=$3;
  RETURN;
END IF;

SELECT ($3 between $1 and $2) or ($4 between $1 and $2);

$_$ language sql;

This causes error

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 109

So I changed code to

CREATE OR REPLACE FUNCTION public.doverlaps(date,
        date, date, date, out bool) IMMUTABLE AS
$_$
SELECT ($3 between $1 and coalesce($2, '99991231')) or
       (coalesce($4, '99991231') between $1 and coalesce($2, '99991231'));
$_$ language sql;

It this best solution ?
How many times this is slower than expression in where clause?

Andrus.

"Andrus" <eetasoft@online.ee> writes:

> This causes error
>
> ERROR: syntax error at or near "IF"
> SQL state: 42601
> Character: 109

SQL has no "IF".  Use plpgsql instead.

> How many times this is slower than expression in where clause?

You can time it. :-)  But I don't believe it will be too slow since it is a
simple operation...

--
Jorge Godoy      <jgodoy@gmail.com>

Andrus wrote:
>> CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date,
>>       date, date, date, out overlaps bool) as
>> $_$
>> SELECT (($3 between $1 and $2) or ($4 between $1 and $2));
>> $_$ language sql;
>
> Thank you.
> In my application second and fourth parameters can be NULL which means
> forever.

No it doesn't. NULL means "unknown". You're just using it to represent
"forever".

There is a value "infinity" for timestamps, but unfortunately not for
dates. Otherwise, I'd suggest that you use that instead.

--
   Richard Huxton
   Archonet Ltd

> In my application second and fourth parameters can be NULL which means
> forever.
> It this best solution ?
> How many times this is slower than expression in where clause?

I am not sure if this would work for you, but instead of using NULL to represent infinity, why not
use 'infinity' to represent infinity?

logs=# select 'infinity'::timestamp;
 timestamp
-----------
 infinity
(1 row)

Regards,

Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:

>> In my application second and fourth parameters can be NULL which means
>> forever.
>> It this best solution ?
>> How many times this is slower than expression in where clause?
>
> I am not sure if this would work for you, but instead of using NULL to
> represent infinity, why not use 'infinity' to represent infinity?

If he casts all his dates to timestamps then this might be a good option.


--
Jorge Godoy      <jgodoy@gmail.com>

> If he casts all his dates to timestamps then this might be a good option.

Thank you.

where  (a::timestamp, coalesce(b, '99991231')::timestamp) overlaps
(c::timestamp, coalesce(d, '99991231')::timestamp)

would be simplest solution.

However

select (date '20050101'::timestamp, date '20060101'::timestamp) overlaps
(date '20060101'::timestamp, date '20070101'::timestamp)

returns false

So this cannot used for date overlapping.
Which sytax to use to substract/add a minute to make this correct?

Andrus.

>> In my application second and fourth parameters can be NULL which means
>> forever.
>
> No it doesn't. NULL means "unknown". You're just using it to represent
> "forever".

My table represents employee absence starting and ending dates.
If end day is not yet known, it is represented by NULL value.
My query should threat unknown value as never ending absence to return
estimated number of work days.

Infinity date value is missing in SQL standard.
I do'nt know any other good way to represent missing ending date.

> There is a value "infinity" for timestamps, but unfortunately not for
> dates. Otherwise, I'd suggest that you use that instead.

I tried to use

timestamp 'infinity':: date

but this does not work if both b and d are infinity since

select timestamp 'infinity':: date<=timestamp 'infinity':: date

returns null.

Andrus.

> I am not sure if this would work for you, but instead of using NULL to
> represent infinity, why not
> use 'infinity' to represent infinity?

Infinity dehaves differenty than ordinal dates and nulls.
If both b and d are infinity then comparison fails:

select timestamp 'infinity':: date<=timestamp 'infinity':: date

returns null.

So infinity introduces third kind of FUD in addition to usual date and
null comparisons. NULLs in SQL are disaster. With infinity  SQL is double
disaster.

In samples I used DATE  '999993112' but this is incorrect.
I must use maximum allowed date or max_timestamp casted to date.
Is it reasonable to use it ?
 I hope that  MAX_DATE <= MAX_DATE returns true.
Which is the value of MAX_DATE is Postgres ?

Andrus.

> My table represents employee absence starting and ending dates.
> If end day is not yet known, it is represented by NULL value.
> My query should threat unknown value as never ending absence to return
> estimated number of work days.
> Infinity date value is missing in SQL standard.
> I do'nt know any other good way to represent missing ending date.
> > There is a value "infinity" for timestamps, but unfortunately not for
> > dates. Otherwise, I'd suggest that you use that instead.
> I tried to use
> timestamp 'infinity':: date
> but this does not work if both b and d are infinity since
> select timestamp 'infinity':: date<=timestamp 'infinity':: date
> returns null.

This might explain why you are getting null;

logs=# select 'infinity'::date;
ERROR:  invalid input syntax for type date: "infinity"

logs=# select 'infinity'::timestamp;
 timestamp
-----------
 infinity
(1 row)

apparently date doesn't know anything about infinity.  However, from what I've read in my "SQL for
smarties" book regarding temporial database design, unknown future dates were stored as:
'9999-12-31'

Would this help, since any enddate with this value would be be enterpreted as an enddate that has
not yet occured?  when you arrive at the date for records effective period to close just update
the enddate to the today's date.

Regards,

Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> writes:
> apparently date doesn't know anything about infinity.

It doesn't, but we have a TODO item to make it do so, which would
presumably include making the timestamp-to-date cast do something
more sensible with an infinity timestamp.

            regards, tom lane

> apparently date doesn't know anything about infinity.  However, from what
> I've read in my "SQL for
> smarties" book regarding temporial database design, unknown future dates
> were stored as:
> '9999-12-31'
>
> Would this help, since any enddate with this value would be be enterpreted
> as an enddate that has
> not yet occured?  when you arrive at the date for records effective period
> to close just update
> the enddate to the today's date.

select date '10000-1-1'< date '99991231'

return false.

If my database contains dates greater than DATE  '9999-12-31' then this
check fails.

This is why I'm searching for a real MAX_DATE value in Postgres.

It would be nice if there will be MAX_DATE constant in Postgres or some one
row system table contains MAX_DATE value.

Andrus.

> > apparently date doesn't know anything about infinity.  However, from what
> > I've read in my "SQL for
> > smarties" book regarding temporial database design, unknown future dates
> > were stored as:
> > '9999-12-31'
> >
> > Would this help, since any enddate with this value would be be enterpreted
> > as an enddate that has
> > not yet occured?  when you arrive at the date for records effective period
> > to close just update
> > the enddate to the today's date.
>
> select date '10000-1-1'< date '99991231'
> return false.
> If my database contains dates greater than DATE  '9999-12-31' then this
> check fails.
> This is why I'm searching for a real MAX_DATE value in Postgres.
> It would be nice if there will be MAX_DATE constant in Postgres or some one
> row system table contains MAX_DATE value.

That is very interesting, but would you really expect to record dates greater than the year 9999?
:o)

Regards,

Richard Broersma Jr.

>> > apparently date doesn't know anything about infinity.  However, from
>> > what
>> > I've read in my "SQL for
>> > smarties" book regarding temporial database design, unknown future
>> > dates
>> > were stored as:
>> > '9999-12-31'
>> >
>> > Would this help, since any enddate with this value would be be
>> > enterpreted
>> > as an enddate that has
>> > not yet occured?  when you arrive at the date for records effective
>> > period
>> > to close just update
>> > the enddate to the today's date.
>>
>> select date '10000-1-1'< date '99991231'
>> return false.
>> If my database contains dates greater than DATE  '9999-12-31' then this
>> check fails.
>> This is why I'm searching for a real MAX_DATE value in Postgres.
>> It would be nice if there will be MAX_DATE constant in Postgres or some
>> one
>> row system table contains MAX_DATE value.
>
> That is very interesting, but would you really expect to record dates
> greater than the year 9999?

Some programmer who did'nt read the book you mentioned but some other sql
book may use
date '10001-1-1' for marking infinity.
So this will break by code.

Andrus.

Andrus wrote:
>> If he casts all his dates to timestamps then this might be a good option.
>
> Thank you.
>
> where  (a::timestamp, coalesce(b, '99991231')::timestamp) overlaps
> (c::timestamp, coalesce(d, '99991231')::timestamp)
>
> would be simplest solution.

I thought the suggested solution was to use infinity, hence the
requirement to cast to timestamps.
That'd mean something along the lines of:

  where  (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps
  (c::timestamp, coalesce(d, 'infinity')::timestamp)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

> I thought the suggested solution was to use infinity, hence the
> requirement to cast to timestamps.
> That'd mean something along the lines of:
>
>  where  (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps
>  (c::timestamp, coalesce(d, 'infinity')::timestamp)

select   (date'20060101'::timestamp, coalesce(date'20060102'::timestamp, 'infinity')) overlaps
  (date'20060102', coalesce(date'20060103'::timestamp, 'infinity'))

returns false but since date'20060102' is overlapping it must return true.
So it seems that it is not possible to use timestamps and infinity.

Andrus.

Andrus wrote:
>> I thought the suggested solution was to use infinity, hence the
>> requirement to cast to timestamps.
>> That'd mean something along the lines of:
>>
>>  where  (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps
>>  (c::timestamp, coalesce(d, 'infinity')::timestamp)
>
> select   (date'20060101'::timestamp, coalesce(date'20060102'::timestamp,
> 'infinity')) overlaps
>  (date'20060102', coalesce(date'20060103'::timestamp, 'infinity'))
>
> returns false but since date'20060102' is overlapping it must return true.
> So it seems that it is not possible to use timestamps and infinity.

Not true, as the above query reads:

  select (date'20060101'::timestamp, date'20060102'::timestamp)
  overlaps
  (date'20060102', date'20060103'::timestamp)

Which doesn't overlap.

What you meant to test is:

  select (date '20060101'::timestamp,
     coalesce(NULL, 'infinity'::timestamp))
  overlaps
  (date '20060102'::timestamp,
     coalesce(NULL, 'infinity'::timestamp))

Which returns true.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

> What you meant to test is:
>
>  select (date '20060101'::timestamp,
>     coalesce(NULL, 'infinity'::timestamp))
>  overlaps
>  (date '20060102'::timestamp,
>     coalesce(NULL, 'infinity'::timestamp))
>
> Which returns true.

Alban,

If first period end and second period start dates are the same, I need that in this case expression
returns true.
Is it possible to implement this using OVERLAPS operator ?

Andrus.

Andrus wrote:
>> What you meant to test is:
>>
>>  select (date '20060101'::timestamp,
>>     coalesce(NULL, 'infinity'::timestamp))
>>  overlaps
>>  (date '20060102'::timestamp,
>>     coalesce(NULL, 'infinity'::timestamp))
>>
>> Which returns true.
>
> Alban,
>
> If first period end and second period start dates are the same, I need
> that in this case expression returns true.
> Is it possible to implement this using OVERLAPS operator ?

You could probably adjust your dates to make OVERLAPS return true, or
you could use the recently suggested way using two BETWEEN statements,
or you could write your own exclusive OVERLAPS operator.


As a "proper" solution, but that requires changing PostgreSQL:

Maybe it is an idea to implement an additional 'flag' to OVERLAPS and
BETWEEN that tells whether the areas to test should be compared
INCLUSIVE or EXCLUSIVE?

I'd imagine something like this.

SELECT (date '20060101', date '20060630') OVERLAPS (date '20060630',
date '20061231') EXCLUSIVE -- The current/default behaviour
---
  f

SELECT (date '20060101', date '20060630') OVERLAPS (date '20060630',
date '20061231') INCLUSIVE
---
  t

And using BETWEEN:

SELECT date '20060101' BETWEEN date '20060101' AND date '20060630' EXCLUSIVE
---
  f

SELECT date '20060101' BETWEEN date '20060101' AND date '20060630'
INCLUSIVE -- The current/default behaviour
---
  t

This reasoning would be valid for any operator working on at least one
range of values.

Next to that, the defaults of OVERLAPS and BETWEEN behaviour being
different may need "fixing" too. Although I realise that this would
break existing implementations, so maybe that's a bad idea.

I suppose the SQL standard specifies the behaviour of these operators,
but adding an optional flag doesn't seem to break compliance. Is this
acceptable?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

>
> If first period end and second period start dates are the same, I need that in this case expression
> returns true.
> Is it possible to implement this using OVERLAPS operator ?
>

I think the best workaround is a function of some kind in whichever
language you choose.  I think you could actually clobber overlaps()
but I chose to give mine a different name.  In my world, all date
ranges have a start, but can have an indefinite end (null).

CREATE OR REPLACE FUNCTION "isoverlap" (date,date,date,date) RETURNS
boolean LANGUAGE pltcl  AS '

set d1 [clock scan $1]
set d3 [clock scan $3]

if {[string length $2] == 0} {
    set d2 0
} else {
    set d2 [clock scan $2]
}
if {[string length $4] == 0} {
    set d4 0
} else {
    set d4 [clock scan $4]
}

if {($d2 >= $d3 && ($d1 <= $d4 || !$d4)) ||
    ($d1 <= $d4 && ($d2 >= $d3 || !$d2)) ||
    (!$d2 && !$d4)} {

    return true
} else {
    return false
}

' ;

> If my database contains dates greater than DATE  '9999-12-31' then this
> check fails.
>
> This is why I'm searching for a real MAX_DATE value in Postgres.
>
> It would be nice if there will be MAX_DATE constant in Postgres or some one
> row system table contains MAX_DATE value.

through expermentation I came up with:

logs=# select '5874897-12-31'::date;
     date
---------------
 5874897-12-31
(1 row)

logs=# select '5874898-12-31'::date;
ERROR:  date out of range: "5874898-12-31"

Regards,

Richard Broersma Jr
l

On Nov 23, 2006, at 10:57 AM, Richard Broersma Jr wrote:
>> If my database contains dates greater than DATE  '9999-12-31' then
>> this
>> check fails.
>>
>> This is why I'm searching for a real MAX_DATE value in Postgres.
>>
>> It would be nice if there will be MAX_DATE constant in Postgres or
>> some one
>> row system table contains MAX_DATE value.
>
> through expermentation I came up with:
>
> logs=# select '5874897-12-31'::date;
>      date
> ---------------
>  5874897-12-31
> (1 row)
>
> logs=# select '5874898-12-31'::date;
> ERROR:  date out of range: "5874898-12-31"

Keep in mind that that number could change depending on if you're
using integer or floating point timestamps.

I know that numeric supports +/- infinity; I don't remember off-hand
if timestamps have that as well.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Jim Nasby wrote:
> I know that numeric supports +/- infinity; I don't remember off-hand if
> timestamps have that as well.

timestamps do, but dates don't.

--
Alban Hertroys
alban@magproductions.nl