Keboola WIKI‎ > ‎Old Articles‎ > ‎Transformations‎ > ‎Redshift‎ > ‎

Generating Date series MySQL vs RedShift

by tomas.trnka@keboola.com   
---

Say you want to generate a list of dates from today minus 365 days.

Here's how you do it in MySQL:

SELECT 

CAST(DATE_ADD((DATE_SUB(DATE(NOW()), INTERVAL 365 DAY)), INTERVAL numbers.number DAY) AS DATE) AS date

FROM (
select
  @i := @i + 1 as number
from
  (select 0 union all select 1 union all select 2 union all
   select 3 union all select 4 union all select 5 union all
   select 6 union all select 7 union all select 8 union all select 9) as t0,
  (select 0 union all select 1 union all select 2 union all
   select 3 union all select 4 union all select 5 union all
   select 6 union all select 7 union all select 8 union all select 9) as t1,
  (select 0 union all select 1 union all select 2 union all
   select 3 union all select 4 union all select 5 union all
   select 6 union all select 7 union all select 8 union all select 9) as t2,
  (select 0 union all select 1 union all select 2 union all
   select 3 union all select 4 union all select 5 union all
   select 6 union all select 7 union all select 8 union all select 9) as t3,
  (select 0 union all select 1 union all select 2 union all
   select 3 union all select 4 union all select 5 union all
   select 6 union all select 7 union all select 8 union all select 9) as t4,
  (select @i:=0) as t_init
) AS numbers 

HAVING CAST(`date` AS DATE)<= DATE(NOW());


And here's how you do the same in Redshift:

SELECT (getdate() - INTERVAL '365 days' + "i") :: DATE
FROM generate_series(1, ((getdate()) :: DATE - (getdate() - INTERVAL '365 days') :: DATE)) "i";

Enjoy :-)


Comments