Nicholas Duffy

Copycopy code to clipboard
1DROP TABLE if exists d_date;
2
3CREATE TABLE d_date
4(
5  date_dim_id              INT NOT NULL,
6  date_actual              DATE NOT NULL,
7  epoch                    BIGINT NOT NULL,
8  day_suffix               VARCHAR(4) NOT NULL,
9  day_name                 VARCHAR(9) NOT NULL,
10  day_of_week              INT NOT NULL,
11  day_of_month             INT NOT NULL,
12  day_of_quarter           INT NOT NULL,
13  day_of_year              INT NOT NULL,
14  week_of_month            INT NOT NULL,
15  week_of_year             INT NOT NULL,
16  week_of_year_iso         CHAR(10) NOT NULL,
17  month_actual             INT NOT NULL,
18  month_name               VARCHAR(9) NOT NULL,
19  month_name_abbreviated   CHAR(3) NOT NULL,
20  quarter_actual           INT NOT NULL,
21  quarter_name             VARCHAR(9) NOT NULL,
22  year_actual              INT NOT NULL,
23  first_day_of_week        DATE NOT NULL,
24  last_day_of_week         DATE NOT NULL,
25  first_day_of_month       DATE NOT NULL,
26  last_day_of_month        DATE NOT NULL,
27  first_day_of_quarter     DATE NOT NULL,
28  last_day_of_quarter      DATE NOT NULL,
29  first_day_of_year        DATE NOT NULL,
30  last_day_of_year         DATE NOT NULL,
31  mmyyyy                   CHAR(6) NOT NULL,
32  mmddyyyy                 CHAR(10) NOT NULL,
33  weekend_indr             BOOLEAN NOT NULL
34);
35
36ALTER TABLE public.d_date ADD CONSTRAINT d_date_date_dim_id_pk PRIMARY KEY (date_dim_id);
37
38CREATE INDEX d_date_date_actual_idx
39  ON d_date(date_actual);
40
41COMMIT;
42
43INSERT INTO d_date
44SELECT TO_CHAR(datum, 'yyyymmdd')::INT AS date_dim_id,
45       datum AS date_actual,
46       EXTRACT(EPOCH FROM datum) AS epoch,
47       TO_CHAR(datum, 'fmDDth') AS day_suffix,
48       TO_CHAR(datum, 'Day') AS day_name,
49       EXTRACT(ISODOW FROM datum) AS day_of_week,
50       EXTRACT(DAY FROM datum) AS day_of_month,
51       datum - DATE_TRUNC('quarter', datum)::DATE + 1 AS day_of_quarter,
52       EXTRACT(DOY FROM datum) AS day_of_year,
53       TO_CHAR(datum, 'W')::INT AS week_of_month,
54       EXTRACT(WEEK FROM datum) AS week_of_year,
55       EXTRACT(ISOYEAR FROM datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(ISODOW FROM datum) AS week_of_year_iso,
56       EXTRACT(MONTH FROM datum) AS month_actual,
57       TO_CHAR(datum, 'Month') AS month_name,
58       TO_CHAR(datum, 'Mon') AS month_name_abbreviated,
59       EXTRACT(QUARTER FROM datum) AS quarter_actual,
60       CASE
61           WHEN EXTRACT(QUARTER FROM datum) = 1 THEN 'First'
62           WHEN EXTRACT(QUARTER FROM datum) = 2 THEN 'Second'
63           WHEN EXTRACT(QUARTER FROM datum) = 3 THEN 'Third'
64           WHEN EXTRACT(QUARTER FROM datum) = 4 THEN 'Fourth'
65           END AS quarter_name,
66       EXTRACT(ISOYEAR FROM datum) AS year_actual,
67       datum + (1 - EXTRACT(ISODOW FROM datum))::INT AS first_day_of_week,
68       datum + (7 - EXTRACT(ISODOW FROM datum))::INT AS last_day_of_week,
69       datum + (1 - EXTRACT(DAY FROM datum))::INT AS first_day_of_month,
70       (DATE_TRUNC('MONTH', datum) + INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month,
71       DATE_TRUNC('quarter', datum)::DATE AS first_day_of_quarter,
72       (DATE_TRUNC('quarter', datum) + INTERVAL '3 MONTH - 1 day')::DATE AS last_day_of_quarter,
73       TO_DATE(EXTRACT(YEAR FROM datum) || '-01-01', 'YYYY-MM-DD') AS first_day_of_year,
74       TO_DATE(EXTRACT(YEAR FROM datum) || '-12-31', 'YYYY-MM-DD') AS last_day_of_year,
75       TO_CHAR(datum, 'mmyyyy') AS mmyyyy,
76       TO_CHAR(datum, 'mmddyyyy') AS mmddyyyy,
77       CASE
78           WHEN EXTRACT(ISODOW FROM datum) IN (6, 7) THEN TRUE
79           ELSE FALSE
80           END AS weekend_indr
81FROM (SELECT '1970-01-01'::DATE + SEQUENCE.DAY AS datum
82      FROM GENERATE_SERIES(0, 29219) AS SEQUENCE (DAY)
83      GROUP BY SEQUENCE.DAY) DQ
84ORDER BY 1;
85
86COMMIT;