5

I have an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.

For example (pseudocode - doesn't work, wrapped for readability):

SELECT ARRAY['2017-01-01'::DATE, '2017-02-03'::DATE] && 
    daterange('2017-01-01', '2017-01-31', '[]');

What this example is trying to show (although it doesn't work), is whether there's an overlap between values between my array of dates and the daterange.

Ideally I could do it with sets, and work out if there's a union between the two, but it doesn't seem possible to cast daterange objects to anything.

I did manage to get this working, but I can't work out how to subsequently reduce the rows:

SELECT daterange('2017-01-01', '2017-01-31', '[]') @> 
    unnest(ARRAY ['2017-01-01' :: DATE, '2016-07-30' :: DATE]);

This returns me multiple rows:

?column?
----------
 t
 f
(2 rows)

Does anyone have any pointers for this? Any help is appreciated.

1
  • What do you mean subsequently how to reduce the rows? Jan 5 '17 at 20:31
6

I've got an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.

Depending on the query, you can use

  • row aggregation with bool_or mentioned by joanolo in his answer.
  • or, you can use ANY with the contains operator @>

    SELECT daterange('2017-01-01', '2017-01-31', '[]')  @> 
      ANY(ARRAY['2017-01-01'::date, '2017-02-03'::date]);
    
    SELECT daterange('2017-01-01', '2017-01-31', '[]')  @> 
      ANY(ARRAY['2016-01-02'::date, '2016-01-09'::date]);
    
2
  • If your dates are already in an array, ANY is probably preferable. If they're not in an array already the bool_or (without unnest is preferable) Jan 5 '17 at 20:49
  • 1
    Oh my god, I was trying to get the ANY operator to work in the console, but was obviously doing something wrong. Looking back through my console history I see I actually tried this, but was calling ANY(unnest(ARRAY[ ... ])) and missed the error message of ERROR: op ANY/ALL (array) requires array on right side
    – aodj
    Jan 5 '17 at 20:55
2

You can use the aggregate function bool_or to achieve what you're looking for:

SELECT
    bool_or(date_range @> a_date) AS some_of_the_dates_lies_within_range
FROM
    (
    SELECT
        unnest(ARRAY['2017-01-01'::DATE, '2017-02-03'::DATE]) AS a_date, 
        daterange('2017-01-01', '2017-01-31', '[]') AS date_range
    ) AS s0
0

If your array of dates is table column and you want to index it, then you might consider generating array from your range on the fly on then find overlap between your array and array from range. This way if you have GIN index on array column, it would be used by query planner.

SELECT ARRAY['2019-01-01'::date, '2019-02-03'::date] && (
    SELECT ARRAY(SELECT d::date
    FROM GENERATE_SERIES(date '2019-01-01', date '2019-01-31', '1 day') AS d)
)

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.