Postgres find gaps between one date range and others

Question

My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one.

In my case is also guaranteed that "date ranges to split by" will not overlap with each other.

Example 1:

Date range to split: 2021-01-01 2021-12-31

Date ranges to split by (1):

• 2021-03-01 2021-06-02

I want to get following output:

• 2021-01-01 2021-02-28
• 2021-06-03 2021-12-31

Example 2:

Date range to split: 2021-01-01 2021-12-31

Date ranges to split by (2):

• 2021-03-01 2021-06-02
• 2021-07-01 2021-12-30

I want to get following output:

• 2021-01-01 2021-02-28
• 2021-06-03 2021-06-30
• 2021-12-31 2021-12-31

Answer 1

The new multirange data types in Postgres 14 are exactly what you need.
Multirange operators allow to split a date range.

The release notes for Postgres 14:

• Add support for multirange data types (Paul Jungwirth, Alexander Korotkov)

  These are like range data types, but they allow the specification of multiple, ordered, non-overlapping ranges. An associated multirange type is automatically created for every range type.

Example 1:

test=> SELECT datemultirange '{[2021-01-01,2022-01-01)}'
test->      - datemultirange '{[2021-03-01,2021-06-02]}' AS result;

                      result
---------------------------------------------------
 {[2021-01-01,2021-03-01),[2021-06-03,2022-01-01)}
(1 row)

Example 2:

test=> SELECT datemultirange '{[2021-01-01,2021-12-31]}'
test->      - datemultirange '{[2021-03-01,2021-06-02], [2021-07-01,2021-12-30]}' AS result;

                                  result
---------------------------------------------------------------------------
 {[2021-01-01,2021-03-01),[2021-06-03,2021-07-01),[2021-12-31,2022-01-01)}
(1 row)

db<>fiddle here

I'm using multirange literals (constants) in my example.
The manual on how to construct multiranges.

Note that [2021-01-01,2022-01-01) and [2021-01-01,2021-12-31] are two equivalent syntax variants for the same the date ranges. The first variant is the canonical form. The manual:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [). User-defined range types can use other conventions, however.