Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.




I am being driven mad. Am reading SQL for Dummies, when I discover an operator called OVERLAPS which is meant to return true or false if 1 time range falls within another time range, brilliant I thought, but can find no meaningfull references r examples to it, only the occasional mention.

My question does OVERLAPS still exist or has it been replaced wit something else


RE: Overlaps

I don't see an OVERLAPS function in Oracle's documentation, but that doesn't mean it doesn't exist.
However, the SQL query should be fairly simple.  If the first timespan is defined by S1 and E1, while the second timespan is defined by S2 and E2, then the following should give you what you need:

SELECT 'overlaps' FROM my_table WHERE
(S1 <= E2 AND E1 >= S2) OR (S2 <= E1 AND E2 >= S1);

RE: Overlaps


Quote (mimer_doc):

5.9.8 The OVERLAPS predicate
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 (see Section 4.6.3) to the value in the first column of the second
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
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.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

RE: Overlaps

Egads!  It DOES exist in Oracle!


13:24:57 SQL> SELECT 'yes' it_exists
13:25:10   2  FROM dual
13:25:16   3  where (sysdate - 5, sysdate) overlaps (sysdate - 2, sysdate - 1);

Thanks for the documentation, PH!  Have a star!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close