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;

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!