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:

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Launch access is open. The single paid plan will be EUR 49 per year.

Get the next issue

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

If you work with enterprise data platforms, migrations, performance tuning, or AI-driven delivery teams, DWHPro Letters is written for you. Get the next issue by email.

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!

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.