DWHPro Insights

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

By Roland Wenzlofsky ·

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

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

OVERLAPS 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.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam