Postgresql Select from date range between array of dates

Question

If this query returns the dates that exists in the range requested.

select created_at from user where created_at between '2015-01-06 00:00:00.000000' and '2015-03-06 00:00:00.000000'

Is there a way to obtain such result but with multiple dates contained with an array of dates.

Just for an example of what im trying to say. i have this array of date, always there's gonna be a first and last date.

Array['2015-01-06 00:00:00.000000','2015-02-10 15:17:18.895000' <- First range
      '2017-10-05 14:41:04.191000','2017-10-11 14:49:36.454000' <- Second range

so is there a way to put a script that goes something like this?

select created_at from win_users 
where (created_at between [First Date] and [Second Date])
or (created_at between [Third Date] and [Fourth Date])

but without using a loop to concat the where statement?

Answer 1

If your array of ranges is always in the format you posted (i.e. 4 items with the first two elements being the first range and the last two the second range), then you can write a query like this:

WITH ranges AS (
    SELECT '{2015-01-06 00:00:00.000000,2015-02-10 15:17:18.895000,2017-10-05 14:41:04.191000,2017-10-11 14:49:36.454000}'::date[] dates
)
    SELECT win_users.created_at FROM win_users, ranges
        WHERE (win_users.created_at > ranges.dates[1] AND win_users.created_at < ranges.dates[2]) OR (win_users.created_at > ranges.dates[3] AND win_users.created_at < ranges.dates[4]);