Excluding (or subtracting) multiple dateranges from a daterange in PostgreSQL

Question

I have a list of items which contain: product_id, period and availability.

The link below references my SQLFiddle Example. I want to get the availability periods of each product for a given period, e.g:

List the availability periods for the period [2018-11-01, 2018-11-30] of the prod_id = 10.

It will display:

 1. [2018-11-01, 2018-11-01]
 2. [2018-11-03, 2018-11-03]
 3. [2018-11-06, 2018-11-07]
 4. [2018-11-10, 2018-11-31]

The operator difference of daterange does not support this type of differences and it returns :

ERROR: result of range difference would not be contiguous

I realized that it is not possible to get the suitable results using the operator with such data.Is there any link or another solution to select the products in this way?

Answer 1

Disjoint ranges.

What essentially you're asking to do is exclude a subset of a range. That creates a "discontinuous ranges" or "disjoint ranges" which can not be contained . It's better to see this with an int,

• ([1,10] - [3,7]) = ([1,2] + [8,10])

These two forms are the same, they're just different ways of writing it. This is simply not supported by the daterange type from the docs,

The union and difference operators will fail if the resulting range would need to contain two disjoint sub-ranges, as such a range cannot be represented.

That's not to say you can't accomplish the same thing just explode the ranges and subtract out the values you don't want.

SELECT d::date
FROM generate_series(
  '2018-11-01'::timestamp without time zone,
  '2018-11-30'::timestamp without time zone,
  '1 day'
) AS gs(d)
WHERE NOT EXISTS (
  SELECT
  FROM test
  WHERE test.period @> d::date
    AND test.available IS FALSE
);

Note always do date-math including date-range generate with timestamp without time zone or you may encounter DST bugs