OVERLAPS

PostgreSQL

(start_time TIMESTAMP, duration INTERVAL) OVERLAPS (other_start_time TIMESTAMP, other_duration INTERVAL)

• start_time timestamp: This parameter specifies the starting time of the first period. It is a data type representing the date and time value.
• duration interval: This parameter represents the length of the first period. It is specified as an interval data type, which represents a period of time.
• other_start_time timestamp: This parameter represents the starting time of the second period. Like start_time TIMESTAMP, it is a data type that represents the date and time value.
• other_duration interval: This parameter represents the length of the second period. Like duration INTERVAL, it represents a period of time and is specified as an interval data type.

Example

CREATE TEMPORARY TABLE temp_table
(
    start_date DATE,
    end_date DATE
);

INSERT INTO temp_table
VALUES
 ('2000-01-01' , '2000-12-31'),
 ('2000-06-01' , '2001-06-01');

SELECT * FROM temp_table
WHERE (start_date, end_date) OVERLAPS ('2000-03-01'::date, '2000-09-01'::date);

Output

 start_date |  end_date
------------+------------
 2000-01-01 | 2000-12-31
 2000-06-01 | 2001-06-01

Explanation

In the above example, PostgreSQL OVERLAPS operator is used to determine if two time periods (defined by start date and end date) overlap with a specific time period. In this case, the given dates (‘2000-03-01’ and ‘2000-09-01’) overlap with the dates in the temporary table, hence both rows are returned in the output.

Oracle

(col1 DATE, col2 DATE) OVERLAPS (col3 DATE, col4 DATE)

• col1 date: This parameter represents the start date of the first date range in Oracle. This can be any valid date value in the database.
• col2 date: This parameter signifies the end date of the first date range. It also needs to be a valid date value within Oracle.
• col3 date: This parameter stands for the start date of the second date range being compared. Like the first two parameters, it should be an accepted date value.
• col4 date: This parameter indicates the end date of the second date range. It is compared to the first range to determine if there are any overlaps. The parameter likewise should be a valid date value.

Example

DECLARE
  start1 DATE := TO_DATE('2022-05-01', 'YYYY-MM-DD');
  end1 DATE := TO_DATE('2022-06-01', 'YYYY-MM-DD');
  start2 DATE := TO_DATE('2022-05-15', 'YYYY-MM-DD');
  end2 DATE := TO_DATE('2022-06-15', 'YYYY-MM-DD');
BEGIN
  IF (start1, end1) OVERLAPS (start2, end2) THEN
    DBMS_OUTPUT.PUT_LINE('Dates are overlapping');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Dates are not overlapping');
  END IF;
END;

Output

Dates are overlapping

Explanation

The OVERLAPS operation is used to determine if two time periods overlapping. Here, start1,end1 represents one time period (May 1, 2022 to June 1, 2022) and start2,end2 represents another (May 15, 2022 to June 15, 2022). Since these time periods overlap, the OVERLAPS operation returns true, and the message ‘Dates are overlapping’ is printed out.