Detecting Overlapping Time Periods with Teradata OVERLAPS Command: A Simple Syntax Guide

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

The Teradata OVERLAPS command simplifies this detection.

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 two intervals must overlap by at least two days for the command to detect an 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 must be at least 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, overlaps are only recognized when the two intervals share at least two days of overlap (for dates) or at least 2 seconds of overlap (for times).

One specific scenario may produce an unexpected result.

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!