Skip to content

Instantly share code, notes, and snippets.

@tomasoak
Last active February 27, 2024 12:28
Show Gist options
  • Save tomasoak/1b3ea2f3cb27004ad30fbb7247794e54 to your computer and use it in GitHub Desktop.
Save tomasoak/1b3ea2f3cb27004ad30fbb7247794e54 to your computer and use it in GitHub Desktop.
Create a date dimension table in AWS Redshift - from 1985 to 2060
begin transaction;
drop table if exists numbers;
create temp table numbers as (
with recursive
counts (number) as (
select 1 as number
union all
select number + 1
from counts m
where number < 27758 -- [!] running dates from 1985-01-01 to 2060-12-31 -- edit here if needed [!]
)
select number
from counts
);
drop table if exists public.dim_date cascade;
create table public.dim_date (
"surrogate_key" int4,
"date" date,
"day_of_week" float8,
"day_of_week_name" varchar(9),
"day_of_month" int4,
"day_of_month_name" varchar(4),
"day_of_year" int4,
"day_of_year_name" varchar(5),
"week" int4,
"iso_week" int4,
"full_week" int4,
"week_name" varchar(4),
"week_end_date" timestamp null,
"week_start_date" timestamp null,
"month" int4,
"month_name" varchar(9),
"month_end_date" timestamp null,
"month_start_date" timestamp null,
"quarter" int4,
"quarter_name" varchar(2),
"half_year" int4,
"half_year_name" varchar(2),
"year" int4,
"year_end_date" timestamp null,
"year_start_date" timestamp null,
"is_weekday" boolean,
"is_weekend" boolean,
"plus_7" date,
"plus_14" date,
"plus_21" date,
"plus_28" date,
"plus_35" date,
"plus_42" date,
"plus_49" date,
"plus_56" date,
"plus_63" date,
"plus_70" date,
"plus_77" date,
"plus_84" date,
"plus_91" date,
"minus_7" date,
"minus_14" date,
"minus_21" date,
"minus_28" date,
"minus_35" date,
"minus_42" date,
"minus_49" date,
"minus_56" date,
"minus_63" date,
"minus_70" date,
"minus_77" date,
"minus_84" date,
"minus_91" date,
"month_day_name_rank" int4,
"month_day_name_reverse_rank" int4,
"us_holiday_identifier" varchar(30),
"is_business_day" boolean
) diststyle all sortkey (date);
insert into public.dim_date
(
surrogate_key,
"date",
day_of_week,
day_of_week_name,
day_of_month,
day_of_month_name,
day_of_year,
day_of_year_name,
week,
iso_week,
week_name,
week_end_date,
week_start_date,
"month",
month_name,
month_end_date,
month_start_date,
quarter,
quarter_name,
half_year,
half_year_name,
"year",
year_end_date,
year_start_date,
is_weekday,
is_weekend
)
with
numbers as (
select *
from numbers
),
bas as (
select
convert(
int, to_char(dateadd(day, num.number, '1985-01-01'), 'YYYYMMDD')
) as surrogate_key,
cast(dateadd(day, num.number, '1985-01-01') as date) as "date",
date_part(dow, dateadd(day, num.number, '1985-01-01'))
+ 1 as day_of_week,
datepart(
day, dateadd(day, num.number, '1985-01-01')
) as day_of_month,
datepart(
doy, dateadd(day, num.number, '1985-01-01')
) as day_of_year,
datepart(week, dateadd(day, num.number, '1985-01-01')) as week,
datepart(month, dateadd(day, num.number, '1985-01-01')) as "month",
datepart(
quarter, dateadd(day, num.number, '1985-01-01')
) as quarter,
case
when datepart(qtr, dateadd(day, num.number, '1985-01-01')) < 3
then 1
else 2
end as half_year,
datepart(year, dateadd(day, num.number, '1985-01-01')) as "year",
case
when
datepart(dow, dateadd(day, num.number, '1985-01-01')) in (
0, 6
)
then 0
else 1
end as is_weekday,
case
when
datepart(dow, dateadd(day, num.number, '1985-01-01')) in (
0, 6
)
then 1
else 0
end as is_weekend
from numbers as num
)
select
bas.surrogate_key,
bas.date,
bas.day_of_week,
case bas.day_of_week
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
end as day_of_week_name,
bas.day_of_month,
convert(varchar(2), bas.day_of_month)
+ case right(convert(varchar(2), bas.day_of_month), 1)
when 1
then
case
when convert(varchar(2), bas.day_of_month) = '11'
then 'th'
else 'st'
end
when 2
then
case
when convert(varchar(2), bas.day_of_month) = '12'
then 'th'
else 'nd'
end
when 3
then
case
when convert(varchar(2), bas.day_of_month) = '13'
then 'th'
else 'rd'
end
when 4 then 'th'
when 5 then 'th'
when 6 then 'th'
when 7 then 'th'
when 8 then 'th'
when 9 then 'th'
when 0 then 'th'
end as day_of_month_name,
bas.day_of_year,
convert(varchar(3), bas.day_of_year)
+ case right(convert(varchar(2), bas.day_of_year), 1)
when 1
then
case
when right(convert(varchar(2), bas.day_of_year), 2) = '11'
then 'th'
else 'st'
end
when 2
then
case
when right(convert(varchar(2), bas.day_of_year), 2) = '12'
then 'th'
else 'nd'
end
when 3
then
case
when right(convert(varchar(2), bas.day_of_year), 2) = '13'
then 'th'
else 'rd'
end
when 4 then 'th'
when 5 then 'th'
when 6 then 'th'
when 7 then 'th'
when 8 then 'th'
when 9 then 'th'
when 0 then 'th'
end as day_of_year_name,
cast(to_char(bas.date, 'WW') as int) as week,
cast(to_char(bas.date, 'IW') as int) as iso_week,
convert(varchar(2), bas.week)
+ case right(convert(varchar(2), bas.week), 1)
when 1
then
case
when convert(varchar(2), bas.week) = '11'
then 'th'
else 'st'
end
when 2
then
case
when convert(varchar(2), bas.week) = '12'
then 'th'
else 'nd'
end
when 3
then
case
when convert(varchar(2), bas.week) = '13'
then 'th'
else 'rd'
end
when 4 then 'th'
when 5 then 'th'
when 6 then 'th'
when 7 then 'th'
when 8 then 'th'
when 9 then 'th'
when 0 then 'th'
end as week_name,
dateadd(
day, 7 - (convert(int, bas.day_of_week)), bas.date
) as week_end_date,
date_trunc('week', bas.date) as week_start_date,
bas.month,
case bas.month
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end as month_name,
last_day(bas.date) as month_end_date,
date_trunc('month', bas.date) as month_start_date,
bas.quarter,
'Q' + convert(varchar(1), bas.quarter) as quarter_name,
bas.half_year,
'H' + convert(varchar(1), bas.half_year) as half_year_name,
bas.year,
dateadd(
day, -1, dateadd(year, +1, date_trunc('year', bas.date))
) as year_end_date,
date_trunc('year', bas.date) as year_start_date,
bas.is_weekday,
bas.is_weekend
from bas;
update public.dim_date
set
plus_7 = dateadd(day, 7, "date"),
plus_14 = dateadd(day, 14, "date"),
plus_21 = dateadd(day, 21, "date"),
plus_28 = dateadd(day, 28, "date"),
plus_35 = dateadd(day, 35, "date"),
plus_42 = dateadd(day, 42, "date"),
plus_49 = dateadd(day, 49, "date"),
plus_56 = dateadd(day, 56, "date"),
plus_63 = dateadd(day, 63, "date"),
plus_70 = dateadd(day, 70, "date"),
plus_77 = dateadd(day, 71, "date"),
plus_84 = dateadd(day, 84, "date"),
plus_91 = dateadd(day, 91, "date"),
minus_7 = dateadd(day, -7, "date"),
minus_14 = dateadd(day, -14, "date"),
minus_21 = dateadd(day, -21, "date"),
minus_28 = dateadd(day, -28, "date"),
minus_35 = dateadd(day, -35, "date"),
minus_42 = dateadd(day, -42, "date"),
minus_49 = dateadd(day, -49, "date"),
minus_56 = dateadd(day, -56, "date"),
minus_63 = dateadd(day, -63, "date"),
minus_70 = dateadd(day, -70, "date"),
minus_77 = dateadd(day, -71, "date"),
minus_84 = dateadd(day, -84, "date"),
minus_91 = dateadd(day, -91, "date")
where "date" < '2060-12-31';
drop table if exists tt_month_rank;
create temp table tt_month_rank as
select
dim_date.date,
row_number()
over (
partition by year, month, day_of_week_name
order by date
)
as month_day_name_rank,
row_number()
over (
partition by year, month, day_of_week_name
order by date desc
)
as month_day_name_reverse_rank
from dim_date;
update public.dim_date
set
month_day_name_rank = tt_month_rank.month_day_name_rank,
month_day_name_reverse_rank = tt_month_rank.month_day_name_reverse_rank
from tt_month_rank
where tt_month_rank.date = dim_date.date;
update public.dim_date
set
"us_holiday_identifier" =
case
when month_name = 'January' and day_of_month = 1
then 'New Years Day'
when
month_name = 'May'
and day_of_week_name = 'Monday'
and month_day_name_reverse_rank = 1
then 'Memorial Day'
when month_name = 'July' and day_of_month = 4
then '4th of July/Independence Day'
when
month_name = 'September'
and day_of_week_name = 'Monday'
and month_day_name_rank = 1
then 'Labor Day'
when
month_name = 'October'
and day_of_week_name = 'Monday'
and month_day_name_rank = 2
then 'Columbus Day'
when month_name = 'November' and day_of_month = 11
then 'Veterans Day'
when
month_name = 'November'
and day_of_week_name = 'Thursday'
and month_day_name_rank = 4
then 'Thanksgiving'
when month_name = 'December' and day_of_month = 25
then 'Christmas'
end;
drop table if exists tt_full_weeks_per_year;
create temporary table tt_full_weeks_per_year as
with
days_per_week_per_year as (
select
"year",
"week",
count(1) days
from public.dim_date
where "date" between '1985-01-01' and '2060-12-31'
group by "year", "week"
)
select
"year",
"week",
row_number()
over (
partition by "year"
order by "week"
)
full_week
from days_per_week_per_year
where days = 7;
update public.dim_date
set full_week = tt_full_weeks_per_year.full_week
from tt_full_weeks_per_year
where
tt_full_weeks_per_year."year" = dim_date."year" and tt_full_weeks_per_year.week = dim_date.week;
update public.dim_date
set
is_business_day =
case
when "us_holiday_identifier" is not null or is_weekend then false
else true
end
;
commit transaction;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment