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?

  • If my answer works for you, I'd appreciate it if you would let me simplify this question so it's better suited for future visitors. We have a policy that asks for the minimal verifiable example, but I'm not going to push it if you want to keep it like this. =) Jul 5, 2018 at 20:54

1 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)
  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

  • The operator && returns the period of overlaps between the given period and the different period of each product.
    – Slim
    Jul 5, 2018 at 19:58
  • Based on your answer if there is no overlap I don't get any result.However I need to display the period of availability of each product.
    – Slim
    Jul 5, 2018 at 20:00
  • I'm totally confused at what you want could you explain a bit more Jul 5, 2018 at 20:04
  • The rows of the example contains a period and column available which is false.E.g: if a product of id = 5 is unavailable from 2018-01-05 to 2018-01-14 and I'm selecting the list of available products from 2018-01-01 to 2018-01-31 I will got such a result the prod_id = 5 and period of availability are: [2018-01-01, 2018-01-04] and [2018-01-15, 2018-01-31]
    – Slim
    Jul 5, 2018 at 20:15
  • The query mentioned in your answer returned a list of dates (availables) during the given period not the available period [start_date, end_date] for each product.
    – Slim
    Jul 6, 2018 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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