The Teradata OVERLAPS Command

The Teradata OVERLAPS Command

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!

Roland Wenzlofsky
 

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 15 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

>