Magic Date and Time dimensions in PostgreSQL for a data warehouse

date_and_time_dimensions.sql

BEGIN;

    CREATE TABLE dwh.d_date
    (
        date_key CHAR(10) PRIMARY KEY NOT NULL,
        full_date DATE NOT NULL
    );
    CREATE UNIQUE INDEX d_date_full_date_uindex ON dwh.d_date (full_date);

    INSERT INTO dwh.d_date
        SELECT date_key, full_date
        FROM (
            SELECT 'UNKNOWN' , DATE '-infinity'
            UNION ALL (
                SELECT
                to_char(full_date,'YYYYMMDD')
                , full_date :: DATE
                FROM generate_series('2000-01-01' :: DATE, '2020-12-31' :: DATE,
                                    '1 DAY' :: INTERVAL) WITH ORDINALITY AS t(full_date, date_key)
            )
            UNION ALL
            SELECT 'INVALID' , DATE 'infinity'
        )  x(date_key, full_date)
    ;

    -- Indices can be added to the main d_date table for any of these functions that
    -- are regularly used.
    CREATE INDEX d_date_year_index ON dwh.d_date (EXTRACT(YEAR FROM full_date));

    CREATE VIEW dwh.d_date_long AS
    SELECT
    date_key
    , full_date

    , EXTRACT(YEAR FROM full_date)                                  AS year_num
    , EXTRACT(MONTH FROM full_date)                                 AS month_num
    , EXTRACT(DAY FROM full_date)                                   AS day_of_month_num

    , EXTRACT(DOY FROM full_date)                                   AS day_of_year_num
    -- ISO calendar week
    ,  EXTRACT(WEEK FROM full_date)                                 AS calendar_week
    , 'Q' || to_char(full_date, 'Q')                                AS quartal
    , to_char(full_date, 'yyyy-"Q"Q')                               AS year_quartal
    , to_char(full_date, 'yyyy-mm')                                 AS year_month
    -- ISO calendar year and week
    , to_char(full_date, 'iyyy-IW')                                 AS year_calendar_week
    -- Weekend
    ,  CASE WHEN EXTRACT(ISODOW FROM full_date) IN ( 6, 7 )
        THEN 'Weekend'
    ELSE 'Weekday' END                                              AS is_weekend
    -- Fixed holidays
    -- for America
    ,  CASE WHEN to_char(full_date, 'MMDD') IN ( '0101', '0704', '1225', '1226' )
        THEN true
    ELSE false END
                                                                    AS is_american_fixed_holiday
    -- ISO start and end of the week of this date
    , full_date + ( 1 - EXTRACT(ISODOW FROM full_date) ) :: INTEGER AS week_start
    , full_date + ( 7 - EXTRACT(ISODOW FROM full_date) ) :: INTEGER AS week_end

    -- Localized Month and Day names
    , to_char(full_date, 'TMMonth')                                 AS month_name
    , to_char(full_date, 'TMMon')                                   AS month_abbr
    , to_char(full_date, 'TMDay')                                   AS weekday_name
    , to_char(full_date, 'TMDy')                                    AS weekday_abbr

    FROM dwh.d_date;


    CREATE TABLE dwh.d_time
    (
        time_key CHAR(8) PRIMARY KEY NOT NULL,
        full_time TIME NOT NULL
    );
    CREATE INDEX d_time_full_time_uindex ON dwh.d_time (full_time);

    INSERT INTO dwh.d_time
        SELECT time_key, full_time
        FROM (
            SELECT 'UNKNOWN' , '24:00:00' :: TIME
            UNION ALL (
                SELECT
                to_char(full_time,'hh24:mi:ss')
                , full_time :: TIME
                FROM generate_series('2000-01-01 00:00:00' :: TIMESTAMP, '2000-01-01 23:59:59' :: TIMESTAMP,
                                    '1 SECOND' :: INTERVAL) AS t(full_time)
            )
            UNION ALL
            SELECT 'INVALID' , '24:00:00' :: TIME
        ) x(time_key, full_time);

    -- Indices can be added to the main d_date table for any of these functions that
    -- are regularly used.
    CREATE INDEX d_time_hour_index ON dwh.d_time (EXTRACT(hour FROM full_time));

    CREATE VIEW dwh.d_time_long AS
    SELECT
        time_key
        , full_time
        -- Hour of the day (0 - 23)
        , EXTRACT(HOUR FROM full_time)                                       AS hour_num
        , EXTRACT(MINUTE FROM full_time)                                     AS minute_num
        , EXTRACT(SECOND FROM full_time)                                     AS second_num
        -- Minute of the day (0 - 1439)
        , EXTRACT(HOUR FROM full_time) * 60 + EXTRACT(MINUTE FROM full_time) AS minute_of_day_num
        , to_char(full_time, 'hh12:mi am')                                   AS time_12_hour
        , to_char(full_time, 'am')                                           AS meridiem_indicator
    FROM dwh.d_time;

COMMIT;