dim_time for postgres

dim_time.sql

DROP TABLE dim_date;

CREATE TABLE dim_date(
      date TIMESTAMP PRIMARY KEY 
    , day_part VARCHAR NOT NULL
	, day_name VARCHAR(10) NOT NULL
	, month_name VARCHAR(10) NOT NULL
	, day_of_week SMALLINT NOT NULL
	, week SMALLINT NOT NULL
    , day SMALLINT NOT NULL
    , month SMALLINT NOT NULL
    , year SMALLINT  NOT NULL
    , semester SMALLINT NOT NULL
    , trimester SMALLINT NOT NULL
    , holiday BOOLEAN NOT NULL -- fix to es_CL
	, format_1 VARCHAR NOT NULL
	, format_2 VARCHAR NOT NULL
	, format_3 VARCHAR NOT NULL
	, format_4 VARCHAR NOT NULL
);

INSERT INTO
    dim_date
    WITH date_series AS (
	SELECT
		GENERATE_SERIES(DATE '2019-01-01', DATE '2019-12-31','1 hour') AS date
	)
    SELECT
		date 
        , CASE	
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '06:00' AND '08:59' THEN 'EARLY MORNING'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '09:00' AND '11:59' THEN 'LATE MORNING'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '12:00' AND '14:59' THEN 'EARLY AFTERNOON'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '15:00' AND '18:59' THEN 'LATE AFTERNOON'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '19:00' AND '21:59' THEN 'EVENING'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '22:00' AND '23:59' THEN 'NIGHT'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '00:00' AND '00:59' THEN 'NIGHT'
		WHEN to_char( date, 'hh24:mi' ) BETWEEN '01:00' AND '05:59' THEN 'DAWN'
        END AS day_part
        , TO_CHAR(date, 'DAY') as day_name
		, TO_CHAR(date, 'TMMONTH') as month_name
		, EXTRACT(isodow from date) as day_of_week
		, EXTRACT('week' from date) as week
        , EXTRACT(DAY FROM date) AS day
        , EXTRACT(MONTH FROM date) AS month
        , EXTRACT(YEAR FROM date) AS year
        , (CASE WHEN (EXTRACT(MONTH FROM date) <= 6) THEN 1 ELSE 2 END) AS semester
        , EXTRACT(QUARTER FROM date) AS trimester
        , (CASE WHEN (EXTRACT(ISODOW FROM date) = 7 OR (EXTRACT(ISODOW FROM date) = 6)) THEN TRUE ELSE FALSE END) AS holiday
		, TO_CHAR(date, 'dd-mm-yyyy')
		, TO_CHAR(date, 'dd/mm/yyyy')
		, TO_CHAR(date, 'yyyy-mm-dd')
		, TO_CHAR(date, 'yyyy/mm/dd')
    FROM 
        date_series;