1

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
2
  • 1
    Please consider following these suggestions.
    – mustaccio
    Nov 28 '21 at 20:04
  • Combine date range to split and date ranges to strip out marking the dates with flag IN/OUT (for date range which you need to split the start is marked as IN, for date ranges for to strip the starts are OUT). Then simply process this dates list and select subranges between IN and adjacent OUT.
    – Akina
    Nov 28 '21 at 20:09
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.

1
  • Thanks a lot! That is exactly what I need :)
    – Dmitry K.
    Nov 28 '21 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.