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!