Creating a date dimension table in PostgreSQL

creating_a_date_dimension_table_in_postgresql.sql

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, 'TMDay') 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,
       EXTRACT(ISOYEAR FROM datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(ISODOW FROM datum) AS week_of_year_iso,
       EXTRACT(MONTH FROM datum) AS month_actual,
       TO_CHAR(datum, 'TMMonth') 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(YEAR 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(YEAR FROM datum) || '-01-01', 'YYYY-MM-DD') AS first_day_of_year,
       TO_DATE(EXTRACT(YEAR 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;

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

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.

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

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

i'm not 100% on week of year ISO definition, but i can't imagine that dates at the beginning and end of the same year would have the same ISO week. consider the following:

Week 52, day 6 and 7 of year 2000 both Jan 1st/2nd and Dec 30/31?

changing the calculation to

, EXTRACT(isoyear from datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(isodow from datum) as week_of_year_iso

yields what i think are the expected result

There's also an error in first/last day of year.

If the date 2000-01-02 is actually in year 1999, then the last day of the year can't possibly be 1999-12-31; it has to be 2000-01-02. Unless you intend that field to always/only be Jan 1/Dec 31 of the year on the timestamp, in which case I'd say that column has literally no value.

Thank you for the comments all. I've made updates that I believe corrects all the addressed, but don't hesitate to let me know if I've missed something.

Changed

EXTRACT(ISOYEAR FROM datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(ISODOW FROM datum) AS week_of_year_iso,
TO_DATE(EXTRACT(YEAR FROM datum) || '-01-01', 'YYYY-MM-DD') AS first_day_of_year,
TO_DATE(EXTRACT(YEAR FROM datum) || '-12-31', 'YYYY-MM-DD') AS last_day_of_year,

Thank you for the script! I made few adjustments to use in Portuguese. Maybe you can improve your script with below changes:

1. Before insert, add set lc_time to 'desired language'; ; eg: set lc_time to 'pt_BR.UTF-8';
2. Change TO_CHAR(datum, 'Day') AS day_name to TO_CHAR(datum, 'TMDay') AS day_name,
3. Change TO_CHAR(datum, 'Month') AS month_name, to TO_CHAR(datum, 'TMMonth') AS month_name,

I hope this can help the script to be improved.

Thank you!

GENERATE_SERIES stop value:

1. 292190 - 2769-12-28
2. 2147483 - 7849-08-11
3. 21474836 - Fails: ERROR: value too long for type character(6)

I am trying to generate dates to 9999-12-31 .NET 5 DateTime.MaxValue Field

What is the maximum date that I can generate?

There seems to be a problem with the first or the last few days of any year having wrong year_actual. Is there a fix for this or am I doing something wrong?

SELECT year_actual, date_actual
FROM dim_date
WHERE year_actual <> EXTRACT(YEAR FROM date_actual);

year_actual	date_actual
2020	2019-12-30
2020	2019-12-31
2020	2021-01-01
2020	2021-01-02
2020	2021-01-03

Edit:
This answer seems to be doing the job

There seems to be a problem with the first or the last few days of any year having wrong year_actual. Is there a fix for this or am I doing something wrong?

SELECT year_actual, date_actual
FROM dim_date
WHERE year_actual <> EXTRACT(YEAR FROM date_actual);

year_actual date_actual
2020 2019-12-30
2020 2019-12-31
2020 2021-01-01
2020 2021-01-02
2020 2021-01-03

Edit:
This answer seems to be doing the job

Thanks. I've updated the gist.

Thank you for the script! I made few adjustments to use in Portuguese. Maybe you can improve your script with below changes:

1. Before insert, add **set lc_time to 'desired language';** ; eg: set lc_time to 'pt_BR.UTF-8';

2. Change **TO_CHAR(datum, 'Day') AS day_name** to **TO_CHAR(datum, 'TMDay') AS day_name,**

3. Change **TO_CHAR(datum, 'Month') AS month_name,** to **TO_CHAR(datum, 'TMMonth') AS month_name,**

I hope this can help the script to be improved.

Thank you!

That's great, thank you. I've updated the gist.

Thank you for the script! I made few adjustments to use in Portuguese. Maybe you can improve your script with below changes:

1. Before insert, add set lc_time to 'desired language'; ; eg: set lc_time to 'pt_BR.UTF-8';
2. Change TO_CHAR(datum, 'Day') AS day_name to TO_CHAR(datum, 'TMDay') AS day_name,
3. Change TO_CHAR(datum, 'Month') AS month_name, to TO_CHAR(datum, 'TMMonth') AS month_name,

I hope this can help the script to be improved.

Thank you!

Just missed one:
4. Change TO_CHAR(datum, 'Mon') AS month_name_abbreviated, to TO_CHAR(datum, 'TMMon') AS month_name_abbreviated,

And maybe a little "comment" to remind people to "manually translate" the quarter names?

Thanks for this anyways, great script!

pls add Financial calendar for a part also. many country's fin date start from jul-1 as start of 1st quarter and next year jun-30 as end of last quarter. year metinon as 2020-21 , 2021-22 .

I love this code! One question, I am wondering if it is possible to extend the table to work from 1900 to say 2049. Is it as simple as setting the start date to 1900-01-01 and then extending the series? I feel like doing so may break some of the logic

I love this code! One question, I am wondering if it is possible to extend the table to work from 1900 to say 2049. Is it as simple as setting the start date to 1900-01-01 and then extending the series? I feel like doing so may break some of the logic

Please try and let us know how it goes!