OVERLAPS Example 1 - Teradata Vantage

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

The result returned is FALSE because two time periods that share a single point do not overlap.

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

In the following example, the arguments 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 DATE periods overlap, so 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 runs on the employee table where period1 and period2 are PERIOD(DATE) columns:

SELECT * FROM employee WHERE period2 OVERLAPS period1;

The employee table contains:

The result is:

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

The employee table contains:

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: