15.00 - OVERLAPS - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1145-015K

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'