Snowflake date and time dimension
Here is a simple SQL script that will generate a date dimension in Snowflake. We are using the built-in GENERATOR and SEQ function. Start date for the dimension is 2017-01-01.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
WITH CTE_MY_DATE AS ( SELECT DATEADD(HOUR, SEQ4(), '2017-01-01 00:00:00') AS MY_DATE FROM TABLE(GENERATOR(ROWCOUNT=>20000)) ) SELECT TO_DATE(MY_DATE) as date ,TO_TIME(MY_DATE) as time ,TO_TIMESTAMP(MY_DATE) as datetime ,YEAR(MY_DATE) as year ,MONTH(MY_DATE) as month ,MONTHNAME(MY_DATE) as monthname ,DAY(MY_DATE) as day ,DAYOFWEEK(MY_DATE) as dayofweek ,WEEKOFYEAR(MY_DATE) as weekofyear ,DAYOFYEAR(MY_DATE) as dayofyear ,HOUR(MY_DATE) as hour FROM CTE_MY_DATE ; |
If you need a little extra and want to include a global holiday calendar in your date dimension you will be glad to hear that we have made a Date Dimension with Global Holiday Calendar dataset available on the marketplace https://www.snowflake.com/datasets/sonra-date-dimension-with-global-holiday-calendar/. If you want to find out more about our dataset go to our blog post where we explain it in more depth https://sonra.io/snowflake/date-dimension-with-global-holiday-calendar/
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
