The Teradata OVERLAPS Function

Roland Wenzlofsky

May 2, 2019

minutes reading time


It is often necessary to determine whether two time periods overlap with historically managed tables. Usually, several comparisons are required.

The Teradata OVERLAPS command makes it more accessible.

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, the selection must overlap at least two days. 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 a date, it also applies to intervals with a 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 two days (date) or 2 seconds (time) must be the difference to recognize an overlap must be considered if necessary.

Finally, there is a particular case that 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!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>