Undocumented OVERLAPS Function, Don't Use it Yet

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:

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:

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:

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:

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