In PostgreSQL, you can use the OVERLAPS operator to test for overlapping time periods.

The function returns true when two time periods (defined by their endpoints) overlap, and false when they do not overlap.

Syntax

It can be used in the following two ways:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

In other words, you provide a start date/time, then you have the option of providing either an end date/time or a length of time.

More specifically, the endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval.

When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start.

Example

Here’s a basic example to demonstrate.

SELECT (date '2022-01-09', date '2022-02-10') OVERLAPS
       (date '2022-02-09', date '2022-03-10');

Result:

True

The result is true because both time periods overlap.

Here it is again, but this time I change the time periods so that they don’t overlap.

SELECT (date '2022-01-09', date '2022-02-08') OVERLAPS
       (date '2022-02-09', date '2022-03-08');

Result:

False

Common Endpoints

It’s important to note that each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means that two time periods with only an endpoint in common do not overlap.

In the next example, the second time period starts on the same day that the first time period ends.

SELECT (date '2022-01-09', date '2022-02-10') OVERLAPS
       (date '2022-02-10', date '2022-03-10');

Result:

False

However, we get a different result if both end points of the first time period are the same:

SELECT (date '2022-01-09', date '2022-01-09') OVERLAPS
       (date '2022-01-09', date '2022-02-10');

Result:

True

Intervals

As mentioned, the second end point can be an interval.

SELECT (date '2022-01-09', interval '32 days') OVERLAPS
       (date '2022-02-09', date '2022-03-10');

Result:

True