Created
November 3, 2021 17:32
-
-
Save shoan/20904d39709de6f1150950223363240d to your computer and use it in GitHub Desktop.
create_a_time_dimension
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- From https://www.etl-tools.com/articles/time-dimension-for-posgresql-based-data-warehouse.html | |
| CREATE TABLE d_time | |
| ( | |
| time_key integer NOT NULL, | |
| time_value character(5) NOT NULL, | |
| hours_24 character(2) NOT NULL, | |
| hours_12 character(2) NOT NULL, | |
| hour_minutes character (2) NOT NULL, | |
| day_minutes integer NOT NULL, | |
| day_time_name character varying (20) NOT NULL, | |
| day_night character varying (20) NOT NULL, | |
| CONSTRAINT d_time_pk PRIMARY KEY (time_key) | |
| ) | |
| WITH ( | |
| OIDS=FALSE | |
| ); | |
| COMMENT ON TABLE d_time IS 'Time Dimension'; | |
| COMMENT ON COLUMN d_time.time_key IS 'Time Dimension PK'; | |
| insert into d_time | |
| SELECT cast(to_char(minute, 'hh24mi') as numeric) time_key, | |
| to_char(minute, 'hh24:mi') AS tume_value, | |
| -- Hour of the day (0 - 23) | |
| to_char(minute, 'hh24') AS hour_24, | |
| -- Hour of the day (0 - 11) | |
| to_char(minute, 'hh12') hour_12, | |
| -- Hour minute (0 - 59) | |
| to_char(minute, 'mi') hour_minutes, | |
| -- Minute of the day (0 - 1439) | |
| extract(hour FROM minute)*60 + extract(minute FROM minute) day_minutes, | |
| -- Names of day periods | |
| case when to_char(minute, 'hh24:mi') BETWEEN '06:00' AND '08:29' | |
| then 'Morning' | |
| when to_char(minute, 'hh24:mi') BETWEEN '08:30' AND '11:59' | |
| then 'AM' | |
| when to_char(minute, 'hh24:mi') BETWEEN '12:00' AND '17:59' | |
| then 'PM' | |
| when to_char(minute, 'hh24:mi') BETWEEN '18:00' AND '22:29' | |
| then 'Evening' | |
| else 'Night' | |
| end AS day_time_name, | |
| -- Indicator of day or night | |
| case when to_char(minute, 'hh24:mi') BETWEEN '07:00' AND '19:59' then 'Day' | |
| else 'Night' | |
| end AS day_night | |
| FROM (SELECT '0:00'::time + (sequence.minute || ' minutes')::interval AS minute | |
| FROM generate_series(0,1439) AS sequence(minute) | |
| GROUP BY sequence.minute | |
| ) DQ | |
| ORDER BY 1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment