Snowflake — Generating dates for a table

Generating dates is a basic task that should be included into any user that comes into Snowflake.

But as this involves working with dates this could be hard, you can follow this guide to help you understand what do you need to do in order to generate the dates that you need.

Generating Dates

There is a function that generates dates based on three parameters.

with date_dim as (
    select to_timestamp_ntz('#From_timestamp','YYYYMMDD hh24:mi:ss') date_id 
), date_range AS (
    SELECT date_id 
    ,DATEADD(HOUR, -#hour_interval*SEQ4(), date_dim.date_id ) as gen_date
    FROM date_dim,TABLE(GENERATOR(ROWCOUNT => (#number_of_times) ))
) 
SELECT  
    to_varchar(gen_date,'YYYY-MM-DD hh24:mi:ss') as date
FROM date_range 
order by 1 asc;

From_timestamp: Timestamp where the date generation starts

Hour_interval : Interval on which you want to generate the dates, for example if you want to generate dates by every 12 hours put 12 there.

Number_of_times: number of dates that you want to generate based on the hour_interval and from_timestamp

Getting number of times

For Number_of_times parameter we need to have the number of dates that we want to generate.