Some time ago I presented a
simple solution to generate date dimension data in PostgreSQL using standard SQL. The below approach is another take on this and probably a little bit easier to read then the previous approach.
This time I made use of the
GENERATE_SERIES function. As a simple example, we first use a
Common Table Expression to generate a dataset which holds 10 successive dates and then in the main query we extract some date information:
CREATE TABLE
test.dwh.dim_date
(
date_tk BIGINT
, date DATE
, day SMALLINT
, month SMALLINT
, quarter SMALLINT
, year INT
, iso8601_year INT
, iso8601_week SMALLINT
, iso8601_dow SMALLINT
)
;
INSERT INTO
test.dwh.dim_date
WITH date_series AS (
SELECT
DATE(GENERATE_SERIES(DATE '2012-01-01', DATE '2014-01-10','1 day')) AS date
)
SELECT
CAST(TO_CHAR(date,'YYYYMMDD') AS INT) AS date_tk
, date
, EXTRACT(DAY FROM date) AS day
, EXTRACT(MONTH FROM date) AS month
, EXTRACT(QUARTER FROM date) AS quarter
, EXTRACT(YEAR FROM date) AS year
-- the ISO 8601 year that the date falls in
-- Each ISO year begins with the Monday of the week containing the 4th of January,
-- so in early January or late December the ISO year may be different from the Gregorian year.
, EXTRACT(ISOYEAR FROM date) AS iso8601_year
-- The number of the week of the year that the day is in. By definition (ISO 8601), weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.
-- In the ISO definition, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year,
-- and for late-December dates to be part of the first week of the next year.
-- For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005,
-- while 2012-12-31 is part of the first week of 2013. It's recommended to use the isoyear field together with week to get consistent results.
, EXTRACT(WEEK FROM date) AS iso8601_week
-- The day of the week as Monday(1) to Sunday(7)
-- This is identical to psql dow except for Sunday (which is 0). This matches the ISO 8601 day of the week numbering.
, EXTRACT(ISODOW FROM date) AS iso8601_dow
FROM
date_series
;
