Create postgresql date dimension

pop_dim_date.sql

create table public.dim_date
(
    id                       integer      not null
        primary key,
    date_value               date         not null
        unique,
    datetime_value           timestamp    not null,
    calendar_year            integer      not null,
    calendar_month           integer      not null,
    calendar_day             integer      not null,
    calendar_quarter         integer      not null,
    calendar_quarter_name    varchar(2)   not null,
    date_text                varchar(100) not null,
    month_name               varchar(10)  not null,
    month_name_short         varchar(3)   not null,
    weekday_name             varchar(10)  not null,
    weekday_name_short       varchar(3)   not null,
    weekday_name_1char       char         not null,
    weekday_of_month         integer      not null,
    day_of_month             integer      not null,
    day_of_month_with_suffix varchar(4)   not null,
    day_of_week              integer      not null,
    day_of_year              integer      not null,
    week_of_month            integer      not null,
    week_of_year             integer      not null,
    start_of_month_id        integer      not null,
    end_of_month_id          integer      not null,
    prior_day_id             integer      not null,
    next_day_id              integer      not null,
    prior_workday_id         integer      not null,
    next_workday_id          integer      not null,
    is_leap_year             boolean      not null,
    is_weekend               boolean      not null,
    is_weekday               boolean      not null,
    is_workday               boolean      not null,
    is_holiday               boolean      not null,
    designation              varchar(500) not null,
    year_month               varchar(7)   not null
);

WITH RECURSIVE date_series AS (
	SELECT '1900-01-01'::DATE AS date_value
	UNION ALL
	SELECT (date_value + INTERVAL '1 day')::DATE
	FROM date_series
	WHERE date_value < '2132-12-31'
	)
INSERT INTO dim_date (
	id, date_value, datetime_value, calendar_year, calendar_month, calendar_day, calendar_quarter,
	calendar_quarter_name, date_text, month_name, month_name_short, weekday_name, weekday_name_short,
	weekday_name_1char, weekday_of_month, day_of_month, day_of_month_with_suffix, day_of_week,
	day_of_year, week_of_month, week_of_year, start_of_month_id, end_of_month_id, prior_day_id,
	next_day_id, prior_workday_id, next_workday_id, is_leap_year, is_weekend, is_weekday, is_workday,
	is_holiday, designation, year_month
)
SELECT
	EXTRACT(YEAR FROM d.date_value) * 10000 + EXTRACT(MONTH FROM d.date_value) * 100 + EXTRACT(DAY FROM d.date_value) AS id,
	d.date_value,
	d.date_value::TIMESTAMP AS datetime_value,
	EXTRACT(YEAR FROM d.date_value) AS calendar_year,
	EXTRACT(MONTH FROM d.date_value) AS calendar_month,
	EXTRACT(DAY FROM d.date_value) AS calendar_day,
	EXTRACT(QUARTER FROM d.date_value) AS calendar_quarter,
	'Q' || EXTRACT(QUARTER FROM d.date_value) AS calendar_quarter_name,
	TO_CHAR(d.date_value, 'FMDay, FMMonth FMDDth, YYYY') AS date_text,  -- Removes leading zero
	TO_CHAR(d.date_value, 'Month') AS month_name,
	TO_CHAR(d.date_value, 'Mon') AS month_name_short,
	TO_CHAR(d.date_value, 'Day') AS weekday_name,
	TO_CHAR(d.date_value, 'Dy') AS weekday_name_short,
	LEFT(TO_CHAR(d.date_value, 'Day'), 1) AS weekday_name_1char,
	FLOOR((EXTRACT(DAY FROM d.date_value) - 1) / 7) + 1 AS weekday_of_month, -- Ensures integer value
	EXTRACT(DAY FROM d.date_value) AS day_of_month,
	TO_CHAR(d.date_value, 'FMDDth') AS day_of_month_with_suffix, -- Removes leading zero
	EXTRACT(ISODOW FROM d.date_value) AS day_of_week,
	EXTRACT(DOY FROM d.date_value) AS day_of_year,
	FLOOR((EXTRACT(DAY FROM d.date_value) - 1) / 7) + 1 AS week_of_month, -- Ensures integer value
	EXTRACT(WEEK FROM d.date_value) AS week_of_year,
	EXTRACT(YEAR FROM d.date_value) * 10000 + EXTRACT(MONTH FROM d.date_value) * 100 + 1 AS start_of_month_id,
	EXTRACT(YEAR FROM d.date_value) * 10000 + EXTRACT(MONTH FROM d.date_value) * 100 +
	EXTRACT(DAY FROM (d.date_value + INTERVAL '1 MONTH - 1 DAY')) AS end_of_month_id,
	 -- Prior day ID
	(EXTRACT(YEAR FROM d.date_value - INTERVAL '1 day') * 10000) +
	(EXTRACT(MONTH FROM d.date_value - INTERVAL '1 day') * 100) +
	(EXTRACT(DAY FROM d.date_value - INTERVAL '1 day')) AS prior_day_id,
	 -- Next day ID
	(EXTRACT(YEAR FROM d.date_value + INTERVAL '1 day') * 10000) +
	(EXTRACT(MONTH FROM d.date_value + INTERVAL '1 day') * 100) +
	(EXTRACT(DAY FROM d.date_value + INTERVAL '1 day')) AS next_day_id,
	 -- Prior workday ID
	(EXTRACT(YEAR FROM
			 CASE
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 1 THEN d.date_value - INTERVAL '3 days'  -- Monday → Friday (-3 days)
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 7 THEN d.date_value - INTERVAL '2 days'  -- Sunday → Friday (-2 days)
				 ELSE d.date_value - INTERVAL '1 day'  -- All other days → Previous day (-1 day)
			 END
	 ) * 10000) +
	(EXTRACT(MONTH FROM
			 CASE
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 1 THEN d.date_value - INTERVAL '3 days'
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 7 THEN d.date_value - INTERVAL '2 days'
				 ELSE d.date_value - INTERVAL '1 day'
			 END
	 ) * 100) +
	(EXTRACT(DAY FROM
			 CASE
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 1 THEN d.date_value - INTERVAL '3 days'
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 7 THEN d.date_value - INTERVAL '2 days'
				 ELSE d.date_value - INTERVAL '1 day'
			 END
	 )) AS prior_workday_id,
	 -- Next workday ID
	(EXTRACT(YEAR FROM
			 CASE
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 5 THEN d.date_value + INTERVAL '3 days'  -- Friday → Monday (+3 days)
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 6 THEN d.date_value + INTERVAL '2 days'  -- Saturday → Monday (+2 days)
				 ELSE d.date_value + INTERVAL '1 day'  -- All other days → Next day (+1 day)
			 END
	 ) * 10000) +
	(EXTRACT(MONTH FROM
			 CASE
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 5 THEN d.date_value + INTERVAL '3 days'
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 6 THEN d.date_value + INTERVAL '2 days'
				 ELSE d.date_value + INTERVAL '1 day'
			 END
	 ) * 100) +
	(EXTRACT(DAY FROM
			 CASE
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 5 THEN d.date_value + INTERVAL '3 days'
				 WHEN EXTRACT(ISODOW FROM d.date_value) = 6 THEN d.date_value + INTERVAL '2 days'
				 ELSE d.date_value + INTERVAL '1 day'
			 END
	 )) AS next_workday_id,
	CASE WHEN EXTRACT(YEAR FROM d.date_value) % 4 = 0 AND
			  (EXTRACT(YEAR FROM d.date_value) % 100 <> 0 OR EXTRACT(YEAR FROM d.date_value) % 400 = 0)
			 THEN TRUE ELSE FALSE END AS is_leap_year,
	CASE WHEN EXTRACT(ISODOW FROM d.date_value) IN (6,7) THEN TRUE ELSE FALSE END AS is_weekend,
	CASE WHEN EXTRACT(ISODOW FROM d.date_value) BETWEEN 1 AND 5 THEN TRUE ELSE FALSE END AS is_weekday,
	CASE WHEN EXTRACT(ISODOW FROM d.date_value) BETWEEN 1 AND 5 THEN TRUE ELSE FALSE END AS is_workday,
	CASE WHEN TO_CHAR(d.date_value, 'MM-DD') IN ('01-01', '07-04', '12-25') OR
			  (EXTRACT(MONTH FROM d.date_value) = 11 AND EXTRACT(ISODOW FROM d.date_value) = 4 AND EXTRACT(DAY FROM d.date_value) BETWEEN 22 AND 28) OR
			  (EXTRACT(MONTH FROM d.date_value) = 5 AND EXTRACT(DOW FROM d.date_value) = 1 AND EXTRACT(DAY FROM d.date_value) BETWEEN 25 AND 31) OR
			  (EXTRACT(MONTH FROM d.date_value) = 9 AND EXTRACT(DOW FROM d.date_value) = 1 AND EXTRACT(DAY FROM d.date_value) BETWEEN 1 AND 7)
			 THEN TRUE ELSE FALSE END AS is_holiday,
	CASE
		WHEN TO_CHAR(d.date_value, 'MM-DD') = '01-01' THEN 'New Year''s Day'
		WHEN TO_CHAR(d.date_value, 'MM-DD') = '07-04' THEN 'Independence Day'
		WHEN TO_CHAR(d.date_value, 'MM-DD') = '12-25' THEN 'Christmas Day'
		WHEN TO_CHAR(d.date_value, 'MM-DD') BETWEEN '11-22' AND '11-28' AND EXTRACT(ISODOW FROM d.date_value) = 4 AND EXTRACT(MONTH FROM d.date_value) = 11 THEN 'Thanksgiving Day'
		WHEN EXTRACT(MONTH FROM d.date_value) = 5 AND EXTRACT(DOW FROM d.date_value) = 1 AND EXTRACT(DAY FROM d.date_value) BETWEEN 25 AND 31 THEN 'Memorial Day'
		WHEN EXTRACT(MONTH FROM d.date_value) = 9 AND EXTRACT(DOW FROM d.date_value) = 1 AND EXTRACT(DAY FROM d.date_value) BETWEEN 1 AND 7 THEN 'Labor Day'
		ELSE ''
	END AS designation,
	TO_CHAR(d.date_value, 'YYYY-MM') AS year_month
FROM date_series d;