Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Creating a date dimension table in PostgreSQL
DROP TABLE if exists d_date;
CREATE TABLE d_date
(
date_dim_id INT NOT NULL,
date_actual DATE NOT NULL,
epoch BIGINT NOT NULL,
day_suffix VARCHAR(4) NOT NULL,
day_name VARCHAR(9) NOT NULL,
day_of_week INT NOT NULL,
day_of_month INT NOT NULL,
day_of_quarter INT NOT NULL,
day_of_year INT NOT NULL,
week_of_month INT NOT NULL,
week_of_year INT NOT NULL,
week_of_year_iso CHAR(10) NOT NULL,
month_actual INT NOT NULL,
month_name VARCHAR(9) NOT NULL,
month_name_abbreviated CHAR(3) NOT NULL,
quarter_actual INT NOT NULL,
quarter_name VARCHAR(9) NOT NULL,
year_actual INT NOT NULL,
first_day_of_week DATE NOT NULL,
last_day_of_week DATE NOT NULL,
first_day_of_month DATE NOT NULL,
last_day_of_month DATE NOT NULL,
first_day_of_quarter DATE NOT NULL,
last_day_of_quarter DATE NOT NULL,
first_day_of_year DATE NOT NULL,
last_day_of_year DATE NOT NULL,
mmyyyy CHAR(6) NOT NULL,
mmddyyyy CHAR(10) NOT NULL,
weekend_indr BOOLEAN NOT NULL
);
ALTER TABLE public.d_date ADD CONSTRAINT d_date_date_dim_id_pk PRIMARY KEY (date_dim_id);
CREATE INDEX d_date_date_actual_idx
ON d_date(date_actual);
COMMIT;
INSERT INTO d_date
SELECT TO_CHAR(datum,'yyyymmdd')::INT AS date_dim_id,
datum AS date_actual,
EXTRACT(epoch FROM datum) AS epoch,
TO_CHAR(datum,'fmDDth') AS day_suffix,
TO_CHAR(datum,'Day') AS day_name,
EXTRACT(isodow FROM datum) AS day_of_week,
EXTRACT(DAY FROM datum) AS day_of_month,
datum - DATE_TRUNC('quarter',datum)::DATE +1 AS day_of_quarter,
EXTRACT(doy FROM datum) AS day_of_year,
TO_CHAR(datum,'W')::INT AS week_of_month,
EXTRACT(week FROM datum) AS week_of_year,
TO_CHAR(datum,'YYYY"-W"IW-') || EXTRACT(isodow FROM datum) AS week_of_year_iso,
EXTRACT(MONTH FROM datum) AS month_actual,
TO_CHAR(datum,'Month') AS month_name,
TO_CHAR(datum,'Mon') AS month_name_abbreviated,
EXTRACT(quarter FROM datum) AS quarter_actual,
CASE
WHEN EXTRACT(quarter FROM datum) = 1 THEN 'First'
WHEN EXTRACT(quarter FROM datum) = 2 THEN 'Second'
WHEN EXTRACT(quarter FROM datum) = 3 THEN 'Third'
WHEN EXTRACT(quarter FROM datum) = 4 THEN 'Fourth'
END AS quarter_name,
EXTRACT(isoyear FROM datum) AS year_actual,
datum +(1 -EXTRACT(isodow FROM datum))::INT AS first_day_of_week,
datum +(7 -EXTRACT(isodow FROM datum))::INT AS last_day_of_week,
datum +(1 -EXTRACT(DAY FROM datum))::INT AS first_day_of_month,
(DATE_TRUNC('MONTH',datum) +INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month,
DATE_TRUNC('quarter',datum)::DATE AS first_day_of_quarter,
(DATE_TRUNC('quarter',datum) +INTERVAL '3 MONTH - 1 day')::DATE AS last_day_of_quarter,
TO_DATE(EXTRACT(isoyear FROM datum) || '-01-01','YYYY-MM-DD') AS first_day_of_year,
TO_DATE(EXTRACT(isoyear FROM datum) || '-12-31','YYYY-MM-DD') AS last_day_of_year,
TO_CHAR(datum,'mmyyyy') AS mmyyyy,
TO_CHAR(datum,'mmddyyyy') AS mmddyyyy,
CASE
WHEN EXTRACT(isodow FROM datum) IN (6,7) THEN TRUE
ELSE FALSE
END AS weekend_indr
FROM (SELECT '1970-01-01'::DATE+ SEQUENCE.DAY AS datum
FROM GENERATE_SERIES (0,29219) AS SEQUENCE (DAY)
GROUP BY SEQUENCE.DAY) DQ
ORDER BY 1;
COMMIT;
@milos-savic-basiq

This comment has been minimized.

Copy link

milos-savic-basiq commented Nov 30, 2017

FROM clause can be simplified to something like:
(SELECT dat::DATE as datum
FROM GENERATE_SERIES('2012-01-01'::DATE, '2029-12-31'::DATE, '1 day') dat) DQ

@tayloramurphy

This comment has been minimized.

Copy link

tayloramurphy commented Jul 9, 2018

This actually gives wrong information for certain dates. 2017-01-01 is shown as having year_actual of 2016. Similarly, the first three days of 2016 are listed in 2015.

@mehulbudasna97

This comment has been minimized.

Copy link

mehulbudasna97 commented Oct 16, 2018

Do you have MySQL version of query to populate data in d_date table? (INSERT Query)

@Szeliga

This comment has been minimized.

Copy link

Szeliga commented Dec 27, 2018

This actually gives wrong information for certain dates. 2017-01-01 is shown as having year_actual of 2016. Similarly, the first three days of 2016 are listed in 2015.

@tayloramurphy according to the documentation this is correct, because the script extracts isoyear:

The ISO 8601 week-numbering year that the date falls in (not applicable to intervals)
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
Result: 2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
Result: 2006
Each ISO 8601 week-numbering 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. See the week field for more information.

Same thing goes for week_of_year column:

week
The number of the ISO 8601 week-numbering week of the year. By definition, ISO 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 week-numbering system, 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.
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7

https://gist.github.com/Szeliga/0cd925bdbbe8e9a2c9c115ab0fcca1ca slightly modified version

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.