Generating date array from several start and end dates-CodePudding

I am trying to generate a dates array in BQ with dates that lie within several start and end dates.

For example, for days between one start and end date it looks like this:

SET DATES = GENERATE_DATE_ARRAY(DATE(2020,02,01), DATE(2022, 04, 25), INTERVAL 1 WEEK);

But how would I generate DATES if I want all these dates included: event_date BETWEEN "2020-02-01" AND "2020-04-25", event_date BETWEEN "2021-02-01" AND "2021-04-25", event_date BETWEEN "2022-02-01" AND "2022-04-25")

I didn't come across any easy fix.

CodePudding user response：

You can just create all those separate arrays and then concat_array() them, no?

CodePudding user response：

Consider below

with dates_ranges as (
  select  "2020-02-01" start_date, "2020-04-25" end_date union all
  select  "2021-02-01", "2021-04-25" union all
  select  "2022-02-01", "2022-04-25" 
)
select date
from dates_ranges, 
unnest(generate_date_array(date(start_date), date(end_date), interval 1 week)) date