Undocumented OVERLAPS Function, Don't Use it Yet

Kevin Meade's picture
articles: 

Recently I have noticed there has been considerable talk on the WEB about date logic; in particular talk about overlapping date ranges and how to detect them. I can't say why there is such an interest, but some people have turned to an undocumented feature (a function called OVERLAPS) to solve their problems. This article will describe date overlap, and show equivelant SQL that will allow you to avoid using this undocumented feature which as always is a good idea, because undocumented features in Oracle have propensity to change and/or disappear unexpectedly leaving those who foolishly realied upon them in unanticipated trouble after an upgrade.

There has been some discussion of overlapping dates on OraFAQ so if you should find my writing leaving you wanting, please try doing a search (if the search engine is not overloaded). As always a google too will do the trick. I would also like to point you to my other favorite Oracle site, ASKTOMHOME where Tom and others give a good treatment of this topic. Most notably I would point out the Tom himself once again cautions against use of undocumented features and since there is an easy alternative for OVERLAPS you should avoid usign OVERLAPS and use the alternative presented here instead until such time as Oracle makes OVERLAPS a real thing (it is after all in ANSI documents for SQL so it must be on its way).

When we talk about dates we will assume we are talking about the traditional Oracle date (with time), think the date format 'DD-MON-RRRR HH24:MI:SS'. A date range has a start_date and an end_date. There are many names for these dates(effective_date,expiration_date is a common example), but in the end we will call them simply start_date and end_date. To be a bit more formal we could say something like: a time period bracketed by a start_date and end_date. It is possible to express this concept as either two dates, or a single date with an offset. But we won't bother with this detail, we will keep it all simple and just deal with the two date formulation.

We can also think about dates in relation to a timeline. This is most effective for date range overlap questions and can easily be visualized with a diagram like this one:

+------------+
        +------------+

If we think of the two lines above as a specification of date ranges, then we can make two easy observations:

Quote:
1. each date range has a start and end point as denoted by plus signs
2. these two date ranges clearly overlap

So... given two date ranges that we can denote as (s1,e1) (s2,e2), we can ask ourselves, what is the full set of possibilities when it comes to how two arbitrary ranges might relate to each other:

+------------+
                +------------+
_________________________________________1
+------------+
             +-------------+
_________________________________________2
+------------+
        +------------+
_________________________________________3
+------------+
      +------+
_________________________________________4
+------------+
   +------+
_________________________________________5



+------------+
+------------+
_________________________________________6



   +------+
+------------+
_________________________________________7
      +------+
+------------+
_________________________________________8
        +------------+
+------------+
_________________________________________9
             +------------+
+------------+
_________________________________________10
                +------------+
+------------+
_________________________________________11

Above are the cases of overlap we can get with two date ranges (excluding special cases we will see later). Looking at some of them we can see that:

Quote:
Case #1 says range two (lower range) does not overlap with range 1 (upper range).

Case #2 says range two shares one common point with range one because the end point of range 1 = start point of range 2.

Case #3 says range two clearly overlaps range 1.

Visually it is easy to see where there is overlap and where there is not. But from this visual inspection we get our first two questions about date range overlap:

Quote:
1) does a date range include its start and end points?
2) does CASE #2 show an overlap or not?

Any interpretation of these questions is a good one as it is not like there is a right or wrong answer to either. However, if someone is going to create a function to do overlap logic, they must answer these questions. We will see in a moment that the undocumented OVERLAPS function answers these questions (particularly question #2) in a way that not everyone would agree with.

Getting to an implementation, let us create some SQL code using the undocumented OVERLAPS function that would demonstrate each of the above cases. For each of the eleven cases above we can construct a query that will return a row or not depending upon if the date ranges in question overlap or not. Here they are:

select  1 from dual where (sysdate,sysdate+1)     overlaps (sysdate+2,sysdate+3);
select  2 from dual where (sysdate,sysdate+1)     overlaps (sysdate+1,sysdate+2);
select  3 from dual where (sysdate,sysdate+1)     overlaps (sysdate+.4,sysdate+2);
select  4 from dual where (sysdate,sysdate+1)     overlaps (sysdate+.4,sysdate+1);
select  5 from dual where (sysdate,sysdate+1)     overlaps (sysdate+.4,sysdate+.7);
select  6 from dual where (sysdate,sysdate+1)     overlaps (sysdate,sysdate+1);
select  7 from dual where (sysdate+.4,sysdate+.7) overlaps (sysdate,sysdate+1);
select  8 from dual where (sysdate+.4,sysdate+1)  overlaps (sysdate,sysdate+1);
select  9 from dual where (sysdate+.4,sysdate+2)  overlaps (sysdate,sysdate+1);
select 10 from dual where (sysdate+1,sysdate+2)   overlaps (sysdate,sysdate+1);
select 11 from dual where (sysdate+2,sysdate+3)   overlaps (sysdate,sysdate+1);

Note the easy syntax of OVERLAPS and how each set of dates does in fact map to the case indicated in the select clause of each query. If we set pagesize 0 and feedback off and run these querys we get the following results:

SQL> set pagesize 0
SQL> set feedback off


SQL> select  1 from dual where (sysdate,sysdate+1)     overlaps (sysdate+2,sysdate+3);
SQL> select  2 from dual where (sysdate,sysdate+1)     overlaps (sysdate+1,sysdate+2);
SQL> select  3 from dual where (sysdate,sysdate+1)     overlaps (sysdate+.4,sysdate+2);
         3
SQL> select  4 from dual where (sysdate,sysdate+1)     overlaps (sysdate+.4,sysdate+1);
         4
SQL> select  5 from dual where (sysdate,sysdate+1)     overlaps (sysdate+.4,sysdate+.7);
         5
SQL> select  6 from dual where (sysdate,sysdate+1)     overlaps (sysdate,sysdate+1);
         6
SQL> select  7 from dual where (sysdate+.4,sysdate+.7) overlaps (sysdate,sysdate+1);
         7
SQL> select  8 from dual where (sysdate+.4,sysdate+1)  overlaps (sysdate,sysdate+1);
         8
SQL> select  9 from dual where (sysdate+.4,sysdate+2)  overlaps (sysdate,sysdate+1);
         9
SQL> select 10 from dual where (sysdate+1,sysdate+2)   overlaps (sysdate,sysdate+1);
SQL> select 11 from dual where (sysdate+2,sysdate+3)   overlaps (sysdate,sysdate+1);
SQL> 

These results clearly show us how the undocumented OVERLAPS works, particulary with respect to CASE #2 (and similarly to CASE #10 which is the same). We know from our visual inspection that CASE #1 (and CASE #11) do not have overlap and so expect no row to return and indeed this is so. We also know that CASES #3,#4,#5,#6 (and also #7,#8,#9) clearly have overlap and so we do expect these cases to return a row, which they do. Lastly, we also know that CASE #2 (and #10) may or may not have overlap depending upon one's particular interpretation. We see clearly that the undocumented OVERLAPS function DOES NOT consider ranges the have only a start and end point in common as ranges that overlap. This is not wrong, simply the behaviour of the function. If you need these cases to calculate as overlapping, then you can not use the undocumented OVERLAPS function; you must instead code the traditional SQL.

So what is the equivelant SQL. Well, if we go back to the visual depiction of our cases, it is a little mind bending but some mental effort will produce the following logic as the specification of overlap that is equivelant to the undocumented OVERLAPS function:

Quote:
where (s2 < e1 and e2 > s1) or (s1 < e2 and e1 > s2)

Applying this logic to our date ranges from our earlier SQL, we get this SQL:

select  1 from dual where (sysdate+2 < sysdate+1 and sysdate+3 > sysdate) or (sysdate < sysdate+3 and sysdate+1 > sysdate+2);
select  2 from dual where (sysdate+1 < sysdate+1 and sysdate+2 > sysdate) or (sysdate < sysdate+2 and sysdate+1 > sysdate+1);
select  3 from dual where (sysdate+.4 < sysdate+1 and sysdate+2 > sysdate) or (sysdate < sysdate+2 and sysdate+1 > sysdate+.4);
select  4 from dual where (sysdate+.4 < sysdate+1 and sysdate+1 > sysdate) or (sysdate < sysdate+1 and sysdate+1 > sysdate+.4);
select  5 from dual where (sysdate+.4 < sysdate+1 and sysdate+.7 > sysdate) or (sysdate < sysdate+.7 and sysdate+1 > sysdate+.4);
select  6 from dual where (sysdate < sysdate+1 and sysdate+1 > sysdate) or (sysdate < sysdate+1 and sysdate+1 > sysdate);
select  7 from dual where (sysdate < sysdate+.7 and sysdate+1 > sysdate+.4) or (sysdate+.4 < sysdate+1 and sysdate+.7 > sysdate);
select  8 from dual where (sysdate < sysdate+1 and sysdate+1 > sysdate+.4) or (sysdate+.4 < sysdate+1 and sysdate+1 > sysdate);
select  9 from dual where (sysdate < sysdate+2 and sysdate+1 > sysdate+.4) or (sysdate+.4 < sysdate+1 and sysdate+2 > sysdate);
select 10 from dual where (sysdate < sysdate+2 and sysdate+1 > sysdate+1) or (sysdate+1 < sysdate+1 and sysdate+2 > sysdate);
select 11 from dual where (sysdate < sysdate+3 and sysdate+1 > sysdate+2) or (sysdate+2 < sysdate+1 and sysdate+3 > sysdate);

Which if we run, we see produces the same results as before:

SQL> select  1 from dual where (sysdate+2 < sysdate+1 and sysdate+3 > sysdate) or (sysdate < sysdate+3 and sysdate+1 > sysdate+2);
SQL> select  2 from dual where (sysdate+1 < sysdate+1 and sysdate+2 > sysdate) or (sysdate < sysdate+2 and sysdate+1 > sysdate+1);
SQL> select  3 from dual where (sysdate+.4 < sysdate+1 and sysdate+2 > sysdate) or (sysdate < sysdate+2 and sysdate+1 > sysdate+.4);
         3
SQL> select  4 from dual where (sysdate+.4 < sysdate+1 and sysdate+1 > sysdate) or (sysdate < sysdate+1 and sysdate+1 > sysdate+.4);
         4
SQL> select  5 from dual where (sysdate+.4 < sysdate+1 and sysdate+.7 > sysdate) or (sysdate < sysdate+.7 and sysdate+1 > sysdate+.4);
         5
SQL> select  6 from dual where (sysdate < sysdate+1 and sysdate+1 > sysdate) or (sysdate < sysdate+1 and sysdate+1 > sysdate);
         6
SQL> select  7 from dual where (sysdate < sysdate+.7 and sysdate+1 > sysdate+.4) or (sysdate+.4 < sysdate+1 and sysdate+.7 > sysdate);
         7
SQL> select  8 from dual where (sysdate < sysdate+1 and sysdate+1 > sysdate+.4) or (sysdate+.4 < sysdate+1 and sysdate+1 > sysdate);
         8
SQL> select  9 from dual where (sysdate < sysdate+2 and sysdate+1 > sysdate+.4) or (sysdate+.4 < sysdate+1 and sysdate+2 > sysdate);
         9
SQL> select 10 from dual where (sysdate < sysdate+2 and sysdate+1 > sysdate+1) or (sysdate+1 < sysdate+1 and sysdate+2 > sysdate);
SQL> select 11 from dual where (sysdate < sysdate+3 and sysdate+1 > sysdate+2) or (sysdate+2 < sysdate+1 and sysdate+3 > sysdate);
SQL> 

Clearly this is not as elegant as the undocumented OVERLAPS, but it is a supported solution. Additonally, if you need CASE #2 and CASE #10 to calculate as overlapping, then you must use this kind of SQL.

If you don't mind the performance hit, you can even write your own OVERLAPS function in PLSQL and then use that. It will look as good as the undocumented OVERLAPS and you can even enhance it to indicate how to handle CASES #2 and #10 if you like.

There are a couple of additional points of interests to speak of. We can also ask ourselves the question: how does the undocumented OVERLAPS function handle a date range that is a single point in time. See these examples for clarification:

+------------+
      +
_________________________________________12
      +
+------------+
_________________________________________13

-- where (s2 < e1 and e2 > s1) or (s1 < e2 and e1 > s2)


select 12 from dual where (sysdate,sysdate+1) overlaps (sysdate+.5,sysdate+.5);
select 12 from dual where (sysdate+.5,sysdate+.5) overlaps (sysdate,sysdate+1);

select 13 from dual where (sysdate+.5 < sysdate+1 and sysdate+.5 > sysdate) or (sysdate < sysdate+.5 and sysdate+1 > sysdate+.5);
select 13 from dual where (sysdate < sysdate+.5 and sysdate+1 > sysdate+.5) or (sysdate+.5 < sysdate+1 and sysdate+.5 > sysdate);

+
+
_________________________________________14

select 14 from dual where (sysdate,sysdate) overlaps (sysdate,sysdate);

select 14 from dual where (sysdate < sysdate and sysdate > sysdate) or (sysdate < sysdate and sysdate > sysdate);

We see that these special cases behave as we would expect given what we have seen so far:

SQL> select 12 from dual where (sysdate,sysdate+1) overlaps (sysdate+.5,sysdate+.5);
        12
SQL> select 12 from dual where (sysdate+.5,sysdate+.5) overlaps (sysdate,sysdate+1);
        12
SQL> select 13 from dual where (sysdate+.5 < sysdate+1 and sysdate+.5 > sysdate) or (sysdate < sysdate+.5 and sysdate+1 > sysdate+.5);
        13
SQL> select 13 from dual where (sysdate < sysdate+.5 and sysdate+1 > sysdate+.5) or (sysdate+.5 < sysdate+1 and sysdate+.5 > sysdate);
        13
SQL> select 14 from dual where (sysdate,sysdate) overlaps (sysdate,sysdate);
SQL> 
SQL> select 14 from dual where (sysdate < sysdate and sysdate > sysdate) or (sysdate < sysdate and sysdate > sysdate);
SQL> 

So... here we have an experimental demonstration of how the undocumented OVERLAPS function works, along with the traditional SQL alternative that does the same thing. Remember, use of undocumented features is generally a bad idea because more than once, Oracle has decided to either change the behaviour of the feature, or drop it entirely; which they are of course free to do because after all, they never told you that it was safe to use it right?

Good luck, Kevin

Comments

A really good article, well written and informative.

I'm going to add one little bit to the discussion. When I read this, I immediately wondered what would happen if the parameters were made more complex. And I wasn't able to find anything other than what was presented.

But then I wondered what would happen if we threw NULLS into the mix. Well, if we try the obvious:

select 1 from dual
where (sysdate, null) overlaps (null, sysdate + 1)

we get "no rows", as expected (at least, that's what I expected). However, when I tried

 select 1 from dual
 where (sysdate, sysdate + 2) overlaps (sysdate + 1, null)
SQL> /

         1
----------
         1

Now this is really what we should get. After all, if one of the endpoints of range 2 is between the endpoints of range 1, then the function should return 'true' no matter what the second endpoint might be. However, I am so used to finding a null parameter making everything null, that I found this to be a bit of a surprise.

Kevin Meade's picture

This is indeed a cool check you did. It demonstrates quite well, the difficulties of working with null. Clearly the "traditional sql" I have provided will not work for this special case involving nulls.

Perhapes someone is willing to offer up a modified piece of logic that will duplicate OVERLAPS including its response to nulls?

Kevin

Thanks Kevin, this will indeed be very useful. OVERLAPS appears to be more of operator than a function though? I'll have to read more about it... Also, I personally wouldn't be too concerned about it being removed/changed as Oracle usually leaves these things alone - but highlighting the risk is quite correct.

--A

Kevin Meade's picture

Yes madkins, there is indeed a shorter way to express the logic. If I had more free time I would take yet another look at it. I thank you for emphasizing the basic ideas of my original post:

Quote:

1) overlaps is undocumented
2) overlaps does not do what most people want anyway

I very much appreciate the time you and TheSingerMan have taken to do the research you have done. Perhapes the best part of this thread is not its description and documentation of OVERLAPS, but rather its testament to basic empirical method and the value of doing your own simple research. In slightly altering the concept to your needs, you and TheSingerMan have added value to the orignal post by extending the conversation to situations others will confront.

Thanks, and once again, good work. Kevin

"'where (s2 < e1 and e2 > s1) or (s1 < e2 and e1 > s2)"

Actually all you need is "where s1 <= e2 and s2 <= e1"

Two ranges [s1,e1] and [s2,e2] (of dates, or integers) overlap if an only if s1 <= e2 and s2 <= e1.

Proof: First, show that if [s1,e1] and [s2,e2] overlap then s1 <= e2 and s2 <= e1.
If the ranges overlap, then there is at least one x that is in both ranges. For this x,

s1 <= x <= e1
AND s2 <= x <= e2

From the above two statements, s1 <= x <= e2, so s1 <=e2
Also s2 <= x <= e1 so s2 <= e1
Thus if [s1,e1] and [s2,e2] overlap then s1 <= e2 and s2 <= e1.

Now show the other way, i.e. that if s1 <= e2 and s2 <= e1 then [s1,e1] and [s2,e2] overlap, i.e. that there must be at least one x such that s1 <= x <=e1 and s2 <= x <= e2

Consider s1 and s2. One of the following statements must be true.

1) s1 = s2
2) s1 < s2
3) s1 > s2

If s1=s2 then we are done because the ranges overlap at s1.

If s1 < s2, recall that we have assumed that s2 <= e1. Thus s1 < s2 <=e1 so s2 is in [s1, e1]. But s2 is in [s2,e2] also so the ranges overlap.

If s1 > s2, recall that we have assumed that s1 <= e2. Thus s2 < s1 <=e2 so s1 is in [s2, e2]. But s2 is in [s1,e1] also so the ranges overlap.

So for any possibility of how s1 and s2 are related, if s1 <= e2 and s2 <= e1 then [s1,e1] and [s2,e2] overlap.

So s1 <= e2 and s2 <= e1 <=> [s1,e1] and [s2,e2] overlap

Hey,
Good article,Thanks for it.
I did came across a simillar task recently.
I have implemented it using PLSQL , I did reffered mine with this article,they have same logic.
Hope many other friends would have had a very simiilar case, very helpful.

OVERLAPS is but one of the operators when working with the PERIOD data type.
There is another operator called MEETS. That is the one that covers your case #2.

Regards,
Alex Friedgan

Thanks for this article- this is a very useful function. I found a potential solution for Case #2:

select 'x' from dual
where (trunc(sysdate), sysdate+10) overlaps (sysdate+10-.1, trunc(sysdate)+15+.1);

Using the -.1 and +.1 on the second range seems to work. We need to compare only dates, not times, so this opens up the range enough to include equal dates, but not enough to include dates outside the range.

The meets function was not recognized by the Oracle 11g version I'm using or I would have used that. Please let me know if you find any problems with the above logic.

Thanks again- Elisabeth

It is good to check two intervals, but if you have more, it becomes a nightmare.

with dates as (
	select 
	to_date('2012-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as poissfrom
	, to_date('2099-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as poissto
	, to_date('2012-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as divfrom
	, to_date('2012-03-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as divto
	, to_date('1900-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as sassfrom
	, to_date('2012-03-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as sassto
    from dual
)
select
poissfrom
, poissto
, divfrom
, divto
, sassfrom
, sassto
, case when
	poissfrom < divto and poissto > divfrom and poissfrom < sassto and poissto > sassfrom and divfrom < sassto and divto > sassfrom
then 'overlap!' else 'does not overlap' end as overlap
from dates;

It needs to check every interval pairs to be sure they are overlapping each other (they have a common intersection).

I think, if I get the maximum value of all start dates, and the minimum value of all end dates, and compare them, the result will be the same.

with dates as (
	select 
	to_date('2012-03-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as poissfrom
	, to_date('2099-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as poissto
	, to_date('2012-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as divfrom
	, to_date('2012-03-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as divto
	, to_date('1900-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as sassfrom
	, to_date('2012-03-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as sassto
    from dual
)
select
poissfrom
, poissto
, divfrom
, divto
, sassfrom
, sassto
, case when
	greatest(poissfrom, divfrom, sassfrom) < least(poissto, divto, sassto)
then 'overlap!' else 'does not overlap' end as overlap
from dates;

Also works with <= if you want to check equality too (CASE #2).