Created
December 24, 2019 22:54
-
-
Save ewhauser/d7dd635ad2d4b20331c7f18038f04817 to your computer and use it in GitHub Desktop.
Generate Date Dimension Table for BigQuery
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
FORMAT_DATE('%F', d) as id, | |
d AS full_date, | |
EXTRACT(YEAR FROM d) AS year, | |
EXTRACT(WEEK FROM d) AS year_week, | |
EXTRACT(DAY FROM d) AS year_day, | |
EXTRACT(YEAR FROM d) AS fiscal_year, | |
FORMAT_DATE('%Q', d) as fiscal_qtr, | |
EXTRACT(MONTH FROM d) AS month, | |
FORMAT_DATE('%B', d) as month_name, | |
FORMAT_DATE('%w', d) AS week_day, | |
FORMAT_DATE('%A', d) AS day_name, | |
(CASE WHEN FORMAT_DATE('%A', d) IN ('Sunday', 'Saturday') THEN 0 ELSE 1 END) AS day_is_weekday, | |
FROM ( | |
SELECT | |
* | |
FROM | |
UNNEST(GENERATE_DATE_ARRAY('2014-01-01', '2050-01-01', INTERVAL 1 DAY)) AS d ) |
Thank u so much!
Thank you for this -- I believe that EXTRACT(DAY FROM d) AS year_day
yields the day number within the month rather than the year. So that column may be better named as month_day
, or changing to use EXTRACT(DAYOFYEAR FROM d)
instead should yield the day number within the year. BigQuery's documentation on EXTRACT is available here for further reference.
Thank you so much for this
This was so helpful!!
Thank you for this.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Excellent !!
Very useful for me