Date dimension view for Redshift

redshift_dim_date.sql

-- Heavily inspired by this StackOverflow thread 
-- https://stackoverflow.com/questions/47230534/how-do-i-create-a-dates-table-in-redshift
-- and tired of bumping my head in the wall after getting the generate_series() error.

DROP VIEW IF EXISTS public.dim_date;
CREATE VIEW public.dim_date AS
  WITH
    digit AS (
      SELECT
        0 AS D
      UNION ALL
      SELECT
        1
      UNION ALL
      SELECT
        2
      UNION ALL
      SELECT
        3
      UNION ALL
      SELECT
        4
      UNION ALL
      SELECT
        5
      UNION ALL
      SELECT
        6
      UNION ALL
      SELECT
        7
      UNION ALL
      SELECT
        8
      UNION ALL
      SELECT
        9
    ),
    seq_table AS (
      SELECT
        a.d + (10 * b.d) + (100 * C.d) + (1000 * D.d) AS num
      FROM
        digit   a
          CROSS JOIN
          digit b
          CROSS JOIN
          digit C
          CROSS JOIN
          digit D
      ORDER BY 1
    ),
    date_seq AS (
      SELECT
        seq.num                             AS seq
        , (GETDATE()::DATE - seq.num)::DATE AS datum
      FROM seq_table AS seq
      WHERE
        datum >= '2015-01-01'                                       ----  <- mind this minimum date lock!
    )
  SELECT
    CAST(seq + 1 AS INTEGER)                                        AS date_id

    -- DATE
    , datum::DATE                                                   AS date_value
    , datum                                                         AS date_as_timestamp
    , TO_CHAR(datum, 'MM/DD/YYYY') :: CHAR(10)                      AS us_format_date

    -- YEAR
    , CAST(EXTRACT(YEAR FROM datum) AS SMALLINT)                    AS year_number
    , CAST(EXTRACT(WEEK FROM datum) AS SMALLINT)                    AS year_week_number
    , CAST(EXTRACT(DOY FROM datum) AS SMALLINT)                     AS year_day_number
    , CAST(TO_CHAR(datum + INTERVAL '3' MONTH, 'yyyy') AS SMALLINT) AS us_fiscal_year_number-- QUARTER

    -- QUARTER
    , CAST(TO_CHAR(datum, 'Q') AS SMALLINT)                         AS qtr_number
    , CAST(TO_CHAR(datum + INTERVAL '3' MONTH, 'Q') AS SMALLINT)    AS us_fiscal_qtr_number-- MONTH

    -- MONTH
    , CAST(EXTRACT(MONTH FROM datum) AS SMALLINT)                   AS month_number
    , TO_CHAR(datum, 'Month')                                       AS month_name
    , CAST(EXTRACT(DAY FROM datum) AS SMALLINT)                     AS month_day_number

    -- WEEK
    , CAST(TO_CHAR(datum, 'D') AS SMALLINT)                         AS week_day_number

    -- DAY
    , TO_CHAR(datum, 'Day')                                         AS day_name
    , CASE
        WHEN TO_CHAR(datum, 'D') IN ('1', '7')
          THEN 0
          ELSE 1
      END                                                           AS day_is_weekday
    , CASE
        WHEN
            EXTRACT(DAY FROM (datum + (1 - EXTRACT(DAY FROM datum)) :: INTEGER +
                              INTERVAL '1' MONTH) :: DATE -
                             INTERVAL '1' DAY) = EXTRACT(DAY FROM datum)
          THEN 1
          ELSE 0
      END                                                           AS day_is_last_of_month
  FROM
    date_seq
  ORDER BY
    date_as_timestamp;