DuckDB: Generate Date Dimension Table

Updated: 2023-10-07

Working with dates is a common and often essential task. DuckDB SQL provides a powerful toolset for extracting valuable insights from date data, helping us understand patterns, trends, and seasonal variations. In this article, let’s take a look into a SQL query that generates a plethora of date-related metrics, allowing you to uncover hidden information within your date data.

The Query

Let’s begin by exploring the SQL query that unravels the world of date dimensions.

Loading editor...

WITH generate_date AS (
    SELECT CAST(RANGE AS DATE) AS the_date 
    FROM RANGE(DATE '2000-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT 
    the_date,
    strftime(the_date, '%Y%m%d') AS date_key,
    DAYOFYEAR(the_date) AS day_of_year, 
    YEARWEEK(the_date) AS week_key,
    WEEKOFYEAR(the_date) AS week_of_year,
    DAYOFWEEK(the_date) AS day_of_week,
    ISODOW(the_date) AS iso_day_of_week,
    DAYNAME(the_date) AS day_name,
    DATE_TRUNC('week', the_date) AS first_day_of_week,
    DATE_TRUNC('week', the_date) + 6 AS last_day_of_week,
    YEAR(the_date) || RIGHT('0' || MONTH(the_date), 2) AS month_key,
    MONTH(the_date) AS month_of_year,
    DAYOFMONTH(the_date) AS day_of_month,
    LEFT(MONTHNAME(the_date), 3) AS month_name_short,
    MONTHNAME(the_date) AS month_name,
    DATE_TRUNC('month', the_date) AS first_day_of_month,
    LAST_DAY(the_date) AS last_day_of_month,
    CAST(YEAR(the_date) || QUARTER(the_date) AS INT) AS quarter_key,
    QUARTER(the_date) AS quarter_of_year,
    CAST(the_date - DATE_TRUNC('Quarter', the_date) + 1 AS INT) AS day_of_quarter,
    ('Q' || QUARTER(the_date)) AS quarter_desc_short,
    ('Quarter ' || QUARTER(the_date)) AS quarter_desc,
    DATE_TRUNC('quarter', the_date) AS first_day_of_quarter,
    LAST_DAY(DATE_TRUNC('quarter', the_date) + INTERVAL 2 MONTH) as last_day_of_quarter,
    CAST(YEAR(the_date) AS INT) AS year_key,
    DATE_TRUNC('Year', the_date) AS first_day_of_year,
    DATE_TRUNC('Year', the_date) - 1 + INTERVAL 1 YEAR AS last_day_of_year,
    ROW_NUMBER() OVER (PARTITION BY YEAR(the_date), MONTH(the_date), DAYOFWEEK(the_date) ORDER BY the_date) AS ordinal_weekday_of_month
FROM generate_date

This query employs a common table expression (CTE) to generate a range of dates, and then it calculates a variety of dimensions for each date within that range.

date_key: A key representing the date.
day_of_year: The day of the year for each date.
week_key: A key representing the week of the year.
week_of_year: The week number within the year.
day_of_week: The day of the week (Sunday as 0, Saturday as 6).
iso_day_of_week: The ISO day of the week (Monday as 1, Saturday as 7).
day_name: The name of the day of the week.
first_day_of_week and last_day_of_week: The first and last days of the week for each date.
month_key: A key representing the year and month.
month_of_year: The month of the year.
day_of_month: The day of the month.
month_name_short and month_name: The short and full names of the month.
first_day_of_month and last_day_of_month: The first and last days of the month for each date.
quarter_key: A key representing the year and quarter.
quarter_of_year: The quarter of the year.
day_of_quarter: The day of the quarter.
quarter_desc_short and quarter_desc: Short and full descriptions of the quarter.
first_day_of_quarter and last_day_of_quarter: The first and last days of the quarter for each date.
year_key: A key representing the year.
first_day_of_year and last_day_of_year: The first and last days of the year for each date.
ordinal_weekday_of_month: The ordinal position of the weekday within the month.

Understanding date dimensions is a fundamental aspect of data analysis. This SQL query empowers you with the ability to extract a wide range of date-related attributes, enabling you to explore categorical patterns, track seasonal variations, and make informed decisions based on your date data.

Feel free to customize and adapt this query to suit your specific data analysis needs.

DuckDB Date functions you might need

How to get the last day of the week in DuckDB

SELECT DATE_TRUNC('week', '2022-01-01') + 6 AS last_day_of_week

How to get abbreviated month name in DuckDB

SELECT LEFT(MONTHNAME('2022-01-01'), 3) AS month_name_short

How to get the month key in DuckDB (YYYYMM)

SELECT YEAR('2022-01-01') || RIGHT('0' || MONTH('2022-01-01'), 2) AS month_key

How to get the quarter key in DuckDB (YYYYQ)

SELECT CAST(YEAR('2022-01-01') || QUARTER('2022-01-01') AS INT) AS quarter_key

How to get the number of days in a quarter in DuckDB

SELECT CAST('2022-01-01' - DATE_TRUNC('Quarter', '2022-01-01') + 1 AS INT) AS day_of_quarter

How to get the last day of the quarter in DuckDB

SELECT LAST_DAY(DATE_TRUNC('quarter', '2022-01-01') + INTERVAL 2 MONTH) as last_day_of_quarter

How to get the last day of the year in DuckDB

SELECT DATE_TRUNC('Year', date_key) - 1 + INTERVAL 1 YEAR AS last_day_of_year