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
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookOVERLAPS does not require two days or two seconds. A positive one-day, one-second or fractional-timestamp overlap matched in the fixture; touching endpoints did not. Reversed boundaries were also tested. Use interval semantics rather than counting inclusive endpoint labels.
SELECT 'Overlapping' WHERE (DATE '2019-01-01', DATE '2019-06-30') OVERLAPS(DATE '2019-06-29', DATE '2019-12-31');
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') ;
SELECT 'Overlapping' (TITLE ' ')WHERE(TIME '03:00:00', TIME '05:00:00') OVERLAPS(TIME '04:59:59', TIME '07:00:00') ;
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') ;
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') ;
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!
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book