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 CEO of Sonra, the data liberation company, he’s on a mission to set data free. A self-proclaimed data evangelist, Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker. Uli is a co-founder of DAMA Ireland, helping build a thriving community of data enthusiasts, and a proud Snowflake Data Superhero, leading the charge as the chapter lead of the Snowflake User Group Ireland. Whether sharing insights, empowering others, or championing the coolest data tools, Uli makes data fun. Growing up in Germany and making Ireland his home, Uli brings a global perspective to everything he does. On the academic side, he holds degrees in political science from Freie Universität Berlin, Albert Ludwigs Universität Freiburg, and the University of Ulster, Coleraine, adding a unique interdisciplinary flair to his work.
