Examples | OVERLAPS Function | Teradata Vantage - 17.10 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)

The following example compares two time periods that share a single common point, CURRENT_TIME.

The result returned is FALSE because when two time periods share a single point, they do not overlap by definition.

SELECT 'OVERLAPS'
WHERE (CURRENT_TIME(0), INTERVAL '1' HOUR)
OVERLAPS (CURRENT_TIME(0), INTERVAL -'1' HOUR);

The following example is nearly identical to the previous one, except that the arguments have been adjusted to overlap by one second. The result is TRUE and the value ‘OVERLAPS’ is returned.

SELECT 'OVERLAPS'
WHERE (CURRENT_TIME(0), INTERVAL '1' HOUR)
OVERLAPS (CURRENT_TIME(0) + INTERVAL '1' SECOND,INTERVAL -'1' HOUR);

The following example uses the datetime_expression, datetime_expression form of OVERLAPS. The two DATE periods overlap each other; thus the result is TRUE.

SELECT 'OVERLAPS'
WHERE (DATE '2000-01-15',DATE '2002-12-15')
OVERLAPS (DATE '2001-06-15',DATE '2005-06-15');

The following example is the same as the previous one, but in row_subquery form:

SELECT 'OVERLAPS'
WHERE (SELECT DATE '2000-01-15', DATE '2002-12-15')
OVERLAPS (SELECT DATE '2001-06-15', DATE '2005-06-15');

The NULL in the following example means the second datetime_expression has a start time of 2001-06-13 15:00:00 and a null end time.

SELECT 'OVERLAPS'
WHERE (TIMESTAMP '2001-06-12 10:00:00', TIMESTAMP '2001-06-15 08:00:00')
OVERLAPS (TIMESTAMP '2001-06-13 15:00:00', NULL);

Because the start time for the second expression falls within the TIMESTAMP interval defined by the first expression, the result is TRUE.

Assume the following query is executed on the employee table where period1 and period2 are PERIOD(DATE) columns:

SELECT * FROM employee WHERE period2 OVERLAPS period1;
Ename period1 period2
Adams ('2005-02-03', '2006-02-03') ('2005-02-03', '2006-02-03')
Mary ('2005-04-02', '2006-01-03') ('2005-02-03', '2006-02-03')
Jones ('2004-01-02', '2004-03-05') ('2004-03-05', '2004-10-07')
Randy ('2004-01-02', '2004-03-05') ('2004-03-07', '2004-10-07')
Simon ? ('2005-02-03', '2005-07-27')

The result is:

Ename period1 period2
Adams ('2005-02-03', '2006-02-03') ('2005-02-03', '2006-02-03')
Mary ('2005-04-02', '2006-01-03') ('2005-02-03', '2006-02-03')

Consider the following table and query:

CREATE TABLE project
   (id INTEGER,
    analysis_phase PERIOD(DATE))
UNIQUE PRIMARY INDEX (id);
INSERT project (1, PERIOD(DATE'2010-06-21',DATE'2010-06-25'));
SELECT 'OVERLAPS'
FROM project
WHERE analysis_phase OVERLAPS 
      PERIOD(DATE'2010-06-24',NULL);

The SELECT statement returns an error because one of the operands of OVERLAP is a Period type with a Period expression specifying an explicit NULL.

Assume that in the employee table, created by the following CREATE TABLE statement, jobdur1 and jobdur2 are derived period columns.

CREATE TABLE employee (
   eid INTEGER NOT NULL, 
   name VARCHAR(100) NOT NULL,
   deptno INTEGER NOT NULL,
   jobst1 DATE NOT NULL,
   jobend1   DATE NOT NULL,
   PERIOD FOR jobdur1(jobst1, jobend1),
   jobst2 DATE NOT NULL,
   jobend2   DATE NOT NULL,
   PERIOD FOR jobdur2(jobst2, jobend2)
   ) PRIMARY INDEX(eid);
EID Name Dept No JobSt1 JobEnd1 JobSt2 JobEnd2
1 Tom 101 DATE'2001-01-01' DATE'2004-01-01' DATE'2005-01-01' DATE'2006-01-01'
2 Rick 201 DATE'2005-01-01' DATE'2006-01-01' DATE'2001-01-01' DATE'2004-01-01'
3 Joo 301 DATE'2005-01-01' DATE'2006-01-01' DATE'2006-01-01' DATE'2007-01-01'
4 Tam 401 DATE'2001-01-01' DATE'2006-01-01' DATE'2002-01-01' DATE'2004-01-01'
5 Pat 501 DATE'2005-01-01' DATE'2007-01-01' DATE'2006-01-01' DATE'2008-01-01'
6 Jack 601 DATE'2005-01-01' DATE'2007-01-01' DATE'2007-01-01' DATE'2008-01-01'
7 Yu 701 DATE'2005-01-01' DATE'2007-01-01' DATE'2004-01-01' DATE'2005-01-01'
8 Tim 801 DATE'2005-01-01' DATE'2007-01-01' DATE'2005-01-01' DATE'2007-01-01'

In the following SQL statement, OVERLAPS is used with derived period columns of the employee table:

SELECT eid, name, jobst1, jobend1, jobst2, jobend2 
FROM employee 
WHERE jobdur1 OVERLAPS jobdur2;

The result is:

EID Name Dept No JobSt1 JobEnd1 JobSt2 JobEnd2
4 Tam 401 DATE'2001-01-01' DATE'2006-01-01' DATE'2002-01-01' DATE'2004-01-01'
5 Pat 501 DATE'2005-01-01' DATE'2007-01-01' DATE'2006-01-01' DATE'2008-01-01'
8 Tim 801 DATE'2005-01-01' DATE'2007-01-01' DATE'2005-01-01' DATE'2007-01-01'