OVERLAPS - Teradata Database

OVERLAPS

Purpose  

Tests whether two time periods overlap each another.

Syntax  

where:

Syntax element …	Specifies …
datetime_expression	a start and end DateTime.
interval_expression	an end DateTime.
row_subquery	an element of a row subquery in a SELECT statement. The subquery cannot specify a SELECT AND CONSUME statement.
period_expression	any expression that evaluates to a Period data type.
derived_period	any expression that evaluates to a derived period.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Time Periods

If the start and end DateTime values in a time period are not ordered chronologically, they are manipulated to make them so prior to making the comparison, using the rule that end_DateTime >= start_DateTime for all cases.

If a time period contains a null start_DateTime and a non-null end_DateTime, then the values are switched to indicate a non-null start_DateTime and a null end_DateTime.

Note: Implicit casting to a Period data type is not supported.

Results

Consider the general case of an OVERLAPS comparison, stated as follows.

   (S1, E1) OVERLAPS (S2, E2)

The result of OVERLAPS is as follows.

   (S1 > S2 AND NOT (S1 >= E2 AND E1 >= E2))

   OR

   (S2 > S1 AND NOT (S2 >= E1 AND E2 >= E1))

   OR

   (S1 = S2 AND (E1 = E2 OR E1 <> E2))

For Period data types or derived periods, where p1 is the first Period expression or derived period and p2 is the second Period expression or derived period, the values of S1, E1, S2, and E2 are as follows:

S1 = BEGIN(p1)

E1 = END(p1)

S2 = BEGIN(p2)

E2 = END(p2)

Rules

When you specify two DateTime types, they must be comparable.

When you specify two Period types, including derived periods, they must be comparable.

If you specify a Period type for either one or both time periods, the Period expression must not include an explicit NULL.

If the first columns of each left and right time periods are DateTime types, they must have the same data type: both DATE, both TIME, or both TIMESTAMP.

If only one time period is a Period type, the first column of the other time period must have the same data type as the element type of the Period.

If neither time period is a Period type, then the second column of each left and right time period must either be the same DateTime type as its corresponding first column or it must be an Interval type that involves only DateTime fields where the precision is such that its value can be added to that of the corresponding DateTime type.

Example  

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);

Example  

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);

Example  

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');

Example  

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');

Example  

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.

Example  

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')

Example  

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.

Example  

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	DeptNo	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	DeptNo	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'