View the original community article here
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.