2

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?

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
Slim
  • 241
  • 3
  • 9
  • 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. =) – Evan Carroll Jul 05 '18 at 20:54

1 Answers1

4

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

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
  • The operator && returns the period of overlaps between the given period and the different period of each product. – Slim Jul 05 '18 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 05 '18 at 20:00
  • I'm totally confused at what you want could you explain a bit more – Evan Carroll Jul 05 '18 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 05 '18 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 06 '18 at 09:02
  • thanks for your answer.However the results returned by your query are a list of separate dates representing the availability. In my case I need a daterange for each product. – Slim Jul 06 '18 at 16:11
  • For each product I will got the daterange of availability. Example prod_id =1 [2018-01-01,2018-01-10] prod_id = 2 [2018-01-05, 2018-01-09]. I think that recursive cte will resolve the problem. – Slim Jul 06 '18 at 16:16