Determining the overlap of two time periods with historically managed tables often requires multiple comparisons.

The Teradata OVERLAPS command improves accessibility.

Here is an example of the OVERLAPS feature:

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

-> Result: ‘Overlapping

Exercise caution when an interval’s ending equals the subsequent interval’s beginning. The OVERLAPS command does not recognize this as 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

The selection must span at least two consecutive days to ensure proper overlap. The query below accurately identifies such overlaps:

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

-> Result: ‘Overlapping

Similar to the case of two date intervals, the overlap between two-time intervals should also be no less than 2 seconds.

The 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

Detecting an overlap requires a minimum of 2 seconds of overlap.

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

-> Result: ‘Overlapping

The OVERLAPS command can 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

The OVERLAPS command detects overlapping intervals through a straightforward syntax. However, it is important to note that overlaps are only recognized when there is a difference of at least two days (date) or 2 seconds (time).

One specific scenario could result in an unforeseen outcome 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 here? Share your thoughts in the comments!

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

You might also like