Generate Date Series/ Create a calendar view of future dates

[LookerExperts]

Knowledge Drop

Last tested: Aug 26, 2019

Some dialects have a generate_series function that makes this trivial. We have a Community article that explains a workaround you can write in SQL for MySQL, Redshift, BigQuery and Snowflake.

For example, Redshift does not implement generate_series(), so we will use the row_number window function over a bunch of cross joins to generate a numerical sequence, and then add the redshift date logic:

SELECT '2015-01-01'::DATE + d AS date

FROM ( SELECT

ROW_NUMBER() OVER () -1 as d

FROM

(SELECT 0 as n UNION SELECT 1) p0,

(SELECT 0 as n UNION SELECT 1) p1,

(SELECT 0 as n UNION SELECT 1) p2,

(SELECT 0 as n UNION SELECT 1) p3,

(SELECT 0 as n UNION SELECT 1) p4,

(SELECT 0 as n UNION SELECT 1) p5,

(SELECT 0 as n UNION SELECT 1) p6,

(SELECT 0 as n UNION SELECT 1) p7,

(SELECT 0 as n UNION SELECT 1) p8,

(SELECT 0 as n UNION SELECT 1) p9,

(SELECT 0 as n UNION SELECT 1) p10

)

This will give you row for every date from Jan 1, 2015 to Aug 9, 2020. Adding an additional (SELECT 0 as ...) will double the number of rows (dates) in the result set.

In BigQuery, to get dates from say 2015-06-01 until today, we could do the following:

SELECT day FROM    UNNEST(GENERATE_DATE_ARRAY(DATE('2015-06-01'), CURRENT_DATE(),    INTERVAL 1 DAY)) AS day

In Snowflake to get 1 year of dates until 2030-01-01:

select
dateadd(day, '-' || seq4(), '2030-01-01') as date
from
table
(generator(rowcount => 366))

This content is subject to limited support.