fvBXCSd1

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