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!
