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

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