The Teradata OVERLAPS Command

0
361
Teradata OVERLAPS

With historically managed tables, it is often necessary to find out whether two time periods overlap. Usually, several comparisons are necessary.

The Teradata OVERLAPS command makes it easier.

Here is an example:

SELECT ‘Overlapping’ WHERE (DATE ‘2019-01-01’, DATE ‘2019-06-30’) OVERLAPS(DATE ‘2019-05-31’, DATE ‘2019-12-31’);

-> Result: ‘Overlapping

Caution is advised when the end of an interval is equal to the start of the second interval. The OVERLAPS command does not consider this to be an overlap:

SELECT ‘Overlapping’ WHERE (DATE ‘2019-01-01’, DATE ‘2019-06-30’) OVERLAPS(DATE ‘2019-06-30’, DATE ‘2019-12-31’);

-> No rows returned

If a date is part of the consideration, at least 2 days must be overlapping. So the following query recognizes the overlap as expected:

SELECT ‘Overlapping’ WHERE (DATE ‘2019-01-01’, DATE ‘2019-06-30’) OVERLAPS(DATE ‘2019-06-29’, DATE ‘2019-12-31’);

-> Result: ‘Overlapping

Analogous, for example, to 2 intervals with date, it also applies to intervals with time that the overlap must be at least 2 seconds:

Thus, the following query does not return an overlap:

SELECT ‘Overlapping’ (TITLE ‘ ‘ ‘)WHERE(TIME ’03:00:00′, TIME ’05:00:00′) OVERLAPS(TIME ’05:00:00′, TIME ’07:00:00’) ;

-> No rows returned

At least 2 seconds of overlap are necessary to detect such an overlap:

SELECT ‘Overlapping’ (TITLE ‘ ‘ ‘)WHERE(TIME ’03:00:00′, TIME ’05:00:00′) OVERLAPS(TIME ’04:59:59′, TIME ’07:00:00’) ;

-> Result: ‘Overlapping

Of course the OVERLAPS command can also be used for TIMESTAMPS:

SELECT ‘Overlapping’ (TITLE ‘ ‘)WHERE(TIMESTAMP ‘2019-01-01 03:00:00’, TIMESTAMP ‘2019-06-30 05:00:00’) OVERLAPS(TIMESTAMP ‘2019-06-30 05:00:00’, TIMESTAMP ‘2019-12-31 07:00:00’) ;

-> No rows returned

SELECT ‘Overlapping’ (TITLE ‘ ‘)WHERE(TIMESTAMP ‘2019-01-01 03:00:00’, TIMESTAMP ‘2019-06-30 05:00:00’) OVERLAPS(TIMESTAMP ‘2019-06-30 04:59:59’, TIMESTAMP ‘2019-12-31 07:00:00’) ;

-> Result: ‘Overlapping

As you can see, the OVERLAPS command provides a simple syntax to detect overlapping intervals. Only the peculiarity that at least 2 days (date) or 2 seconds (time) must be difference to recognize an overlap must be considered if necessary.

Finally, there is a special case, which may lead to an unexpected result for you:

SELECT ‘Overlapping’ (TITLE ‘ ‘ ‘)WHERE(TIME ’05:00:00′, TIME ’03:00:00′) OVERLAPS(TIME ’03:00:01′, TIME ’04:00:00’) ;

What do you think is being delivered back here? Leave a comment with your solution!

Our Reader Score
[Total: 5    Average: 4/5]
The Teradata OVERLAPS Command written by Roland Wenzlofsky on May 2, 2019 average rating 4/5 - 5 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here