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