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?

In your pseudo-query, shouldn't it be or instead of and? Otherwise the query is not going to return anything unless your ranges overlap. — kaveh
– kaveh, Commented Jan 13, 2019 at 0:20
my bad, in the second query i forget to put the or statement. I edit the question with the suggested changes. — Luis Cardoza Bird
– Luis Cardoza Bird, Commented Jan 13, 2019 at 0:31

klin · Accepted Answer · 2020-10-19 16:05:40Z

4

An array of dates is very uncomfortable in this case. Use arrays of daterange and the containtment operator <@, e.g.:

with my_table(id, created_at) as (
values 
    (1, '2015-01-10'::timestamp),
    (2, '2016-05-10'),
    (3, '2017-10-10')
)

select *
from my_table
where created_at::date <@ any(array[
    daterange('2015-01-06','2015-02-10'), 
    daterange('2017-10-05','2017-10-11')])

 id |     created_at      
----+---------------------
  1 | 2015-01-10 00:00:00
  3 | 2017-10-10 00:00:00
(2 rows)

If you absolutely want to use an array of dates (honestly I do not think so), use this function to convert it to daterange array:

create or replace function date_pairs_to_ranges(date[])
returns daterange[] language sql as $$
    select array_agg(daterange(d1, d2))
    from unnest($1) with ordinality as u1(d1, o1)
    join unnest($1) with ordinality as u2(d2, o2)
    on o1/ 2* 2 < o1 and o2 = o1+ 1
$$;

with my_table(id, created_at) as (
values 
    (1, '2015-01-10'::timestamp),
    (2, '2016-05-10'),
    (3, '2017-10-10')
)

select *
from my_table
where created_at::date <@ any(
    date_pairs_to_ranges(array[
        '2015-01-06','2015-02-10',
        '2017-10-05','2017-10-11']::date[]))

edited Oct 19, 2020 at 16:05

answered Jan 13, 2019 at 1:09

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luis Cardoza Bird Over a year ago

Im gonna give it a try, the explanation was smooth so im sure this Will work

Luis Cardoza Bird Over a year ago

You sir are a genius :) thanks! Works perfect @klin

kaveh · Accepted Answer · 2019-01-13 00:51:59Z

0

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]);

answered Jan 13, 2019 at 0:51

kaveh

2,1561 gold badge22 silver badges37 bronze badges

1 Comment

Luis Cardoza Bird Over a year ago

But if I use this, then im gonna need a loop for the array. And I wanna Avoid that case :/

Collectives™ on Stack Overflow

Postgresql Select from date range between array of dates

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related