msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Cool Undocumented OVERLAPS Predicate

David Aldridge’s post about OVERLAPS caught my attention. I did not know that you could do something like this in Oracle:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 22:06:24 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect eddie/awad
Connected.
SQL>  SELECT *
  2     FROM dual
  3    WHERE (add_months(sysdate, -6), DATE '2008-08-08')
  4          OVERLAPS
  5          (sysdate - 180, interval '2' YEAR);

D
-
X

Indeed, like David, I searched the Oracle docs and I could not find anything describing OVERLAPS. I extended my search to the OTN Forums and I got these hits:

So, what does OVERLAPS really do? according to Mimer SQL Reference Manual:

The OVERLAPS predicate tests whether two “events” cover a common point in time or not, and has the form:

(expression, expression) OVERLAPS (expression, expression)

Each of the two “events” specified on either side of the OVERLAPS keyword is a period of time between two specified points on the timeline. The two points can be specified as a pair of datetime values or as one datetime value and an INTERVAL offset.

Each “event” is defined by a two expressions constituting a row value expression having two columns.

The first column in each row value expression must be a DATE, TIME or TIMESTAMP and the value in the first column of the first “event” must be comparable.

The second column in each row value expression may be either a DATE, TIME or TIMESTAMP that is comparable with the value in the first column or an INTERVAL with a precision that allows it to be added to the value in the first column.

The value in the first column of each row value expression defines one of the points on the timeline for the event.

If the value in the second column of the row value expression is a datetime, it defines the other point on the timeline for the event.

If the value in the second column of the row value expression is an INTERVAL, the other point on the timeline for the event is defined by adding the values in the two column of the row value to expression together.

The NULL value is assumed to be a point that is infinitely late in time.

Either of the two points may be the earlier point in time.

If the value in the first column of the row value expression is the NULL value, then this is assumed to be the later point in time.

I wonder if or when a similar documentation will be added to the Oracle SQL Reference.

Warning: Undocumented features should not be used in production systems.


Filed in Oracle on 12 Jun 08 | Tags: ,


Reader's Comments

  1. |

    WM_OVERLAPS is documented

    SELECT * FROM dual WHERE WM_OVERLAPS(WM_PERIOD(add_months(sysdate, -6), DATE ‘2008-08-08′), WM_PERIOD(sysdate – 180, sysdate+180))=1;

  2. |

    For many years I have had an OVERLAP_CNT(start_date1 date, end_date1 date, start_date2 date, end_date2 date) function in my CMP package, which is available on my website. This returns a 1 if the date range represented by the first two dates overlaps the date range represented by the second two dates, and a 0 if it does not. This allows you to use it in a sum() to count how many rows have an overlapping date range, or a where clause.

  3. |

    @Laurent: I’m assuming that the WM_OVERLAPS needs the Workspace Manager to be installed/enabled.

    @John: OVERLAP_CNT seems useful. Feel free to share the link to the code on your website.