Overlap flags (Was: Re: Why overlaps is not working)

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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andrus
> Sent: Thursday, November 09, 2006 2:47 PM
> To: pgsql-general(at)postgresql(dot)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

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.

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(at)magproductions(dot)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(at)online(dot)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(at)gmail(dot)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 Arajo 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(at)online(dot)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(at)gmail(dot)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(at)online(dot)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(at)gmail(dot)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(at)yahoo(dot)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(at)gmail(dot)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.

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

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

> 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(at)yahoo(dot)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(at)magproductions(dot)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(at)magproductions(dot)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(at)magproductions(dot)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(at)nasby(dot)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(at)magproductions(dot)nl