CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Adithya Somanathan
Adithya Somanathan

Reputation: 37

How to create a time series when start and end dates are given in a snowflake environment?

Given a table with columns for start and end dates, I'm trying to get individual row entries in the form of a time series with all dates in-between the mentioned dates

I have tried a piece of code from here but https://community.periscopedata.com/t/18wkh8/generate-series-of-dates-in-snowflake

select dateadd(day, '-' || seq4(), current_date()) as dte
from table (generator(rowcount => (Difference between start and end date))

Input:
Start_date End_date    Value
 2019-01-01 2019-01-15  1$
 2019-01-16 2019-01-23  2$
Output:
 Date        Value 
 2019-01-01   1$
 2019-01-02   1$ 
 ---- so on 
 2019-01-05   1$
 2019-01-16   2$ 
 --- so on 
 2019-01-23   2$

Upvotes: 1

Views: 2350

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

There's a problem with Stuart's answer: seq4() is not guaranteed to give sequential numbers -- you could end with a series of disparate dates.

Instead, you could do:

select -1 + row_number() over(order by 0) i, start_date + i generated_date 
from (select '2020-01-01'::date start_date, '2020-01-15'::date end_date)
join table(generator(rowcount => 10000 )) x
qualify i < 1 + end_date - start_date

Upvotes: 1

Stuart Ozer
Stuart Ozer

Reputation: 1384

The rowcount => needs to be a constant, but you can make it large enough and filter it later without it taking noticeable execution time. For example the following works if DTS is the name of your table with the date ranges:

with 
   maxdiff as (
      select max(datediff(day, start_date, end_date)) days
      from dts),
   cal as (
      select seq4() n 
      from table(generator(rowcount => 10000))
   )
select 
   dateadd(day, n, start_date) theDate, 
   value
from dts join cal
where n <= (select days+1 from maxdiff)
      and theDate >= start_date and theDate <= end_date
order by theDate
;

Upvotes: 2

Related Questions