We have migrated to new Google sites!
Below are two functions that can be called to build and populate a date or time dimension. Feel free to strip it for parts. You never know when it may come in handy.
--- Call the function that builds and populates the dimension.
select buildDateDim (date '1999-01-01', date '2013-12-31') as "Date Dim Builder"
select * from dim_date order by 1;
--- Create the builder function
CREATE OR REPLACE FUNCTION buildDateDim(startdate date, enddate date)
RETURNS text AS
$BODY$
DECLARE
strresult text default ('Table Created!');
loopDate date default (startDate);
BEGIN
execute 'drop table if exists dim_date';
execute 'create table dim_date (
date_key integer
, date_year smallint
, quarter_name char(2)
, year_quarter char(7)
, month_name varchar(15)
, month_name_short char(3)
, month_number smallint
, year_month varchar(7)
, date_value date
, day_name varchar(11)
, day_of_week_num smallint
, day_of_year smallint
, week_of_year smallint
, day_type varchar(10)
, date_formal varchar(20)
, last_30days_ind smallint
, last_60days_ind smallint
, last_90days_ind smallint
, rec_update_date date
) distributed by (date_key)';
while loopDate <= endDate loop
execute 'insert into dim_date values (' ||
to_number(to_char(loopDate, 'yyyymmdd'), 99999999) || ', ' ||
extract(year from loopDate) || ', ' ||
'''Q' || extract(quarter from loopDate) || ''', ' ||
'''' || rtrim(to_char(loopDate, 'yyyy')) || '-Q' || extract(quarter from loopDate) || ''', ' ||
'''' || rtrim(to_char(loopDate, 'Month')) || ''', ' ||
'''' || rtrim(to_char(loopDate, 'MON')) || ''', ' ||
to_number(to_char(loopDate, 'MM'), 99) || ', ' ||
'''' || rtrim(to_char(loopDate, 'yyyy-MM')) || ''', ' ||
'''' || loopDate || ''', ' ||
'''' || rtrim(to_char(loopDate, 'Day')) || ''', ' ||
extract(isodow from loopDate) || ', ' ||
to_number(to_char(loopDate, 'DDD'), 999) || ', ' ||
to_number(to_char(loopDate, 'WW'), 99) || ', ' ||
'''' || case when
rtrim(to_char(loopDate, 'Day')) in ('Saturday', 'Sunday') then 'Weekend' else 'Weekday'
end || ''', ' ||
'''' || rtrim(to_char(loopDate, 'FMMonth FMDDth, yyyy')) || ''', ' ||
case when
loopDate between (now()::date - interval '30 day') and now()::date then 1 else 0
end || ', ' ||
case when
loopDate between (now()::date - interval '60 day') and now()::date then 1 else 0
end || ', ' ||
case when
loopDate between (now()::date - interval '90 day') and now()::date then 1 else 0
end || ', ' ||
'''' || now()::date || '''' ||
')';
loopDate := loopDate + 1;
end loop;
RETURN strresult;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION builddatedim(date, date) OWNER TO gpadmin;
------
------
--- Call the function that builds and populates the dimension.
select buildTimeDim () as "Time Dim Builder"
select * from dim_time order by 1
--- Create the builder function
CREATE OR REPLACE FUNCTION buildTimeDim()
RETURNS text AS
$BODY$
DECLARE
strresult text default ('Table Created!');
loopTime time default ('0:00');
BEGIN
execute 'drop table if exists dim_time';
execute 'create table dim_time (
time_key char(4)
, hour_minute time
, time_ampm varchar(8)
, hour_range_text varchar(20)
, day_segment varchar(20)
) distributed by (time_key)';
for i in 0..1439 loop
execute 'insert into dim_time values (' ||
'''' || to_char(loopTime, 'hh24mi') || ''', ' ||
'''' || to_char(loopTime, 'hh24:mi') || ''', ' ||
'''' || to_char(loopTime, 'hh:miam') || ''', ' ||
'''' || case rtrim(to_char(loopTime, 'hh24'))
when '00' then '12am-12:59am' when '01' then '1am-1:59am' when '02' then '2am-2:59am'
when '03' then '3am-3:59am' when '04' then '4am-4:59am' when '05' then '5am-5:59am'
when '06' then '6am-6:59am' when '07' then '7am-7:59am' when '08' then '8am-8:59am'
when '09' then '9am-9:59am' when '10' then '10am-10:59am' when '11' then '11am-11:59am'
when '12' then '12pm-12:59pm' when '13' then '1pm-1:59pm' when '14' then '2pm-2:59pm'
when '15' then '3pm-3:59pm' when '16' then '4pm-4:59pm' when '17' then '5pm-5:59pm'
when '18' then '6pm-6:59pm' when '19' then '7pm-7:59pm' when '20' then '8pm-8:59pm'
when '21' then '9pm-9:59pm' when '22' then '10pm-10:59pm' when '23' then '11pm-11:59pm'
else 'other' end || ''', ' ||
'''' || case
when rtrim(to_char(loopTime, 'hh24')) between '00' and '04' then 'Late Night'
when rtrim(to_char(loopTime, 'hh24')) between '05' and '07' then 'Early Morning'
when rtrim(to_char(loopTime, 'hh24')) between '08' and '11' then 'Morning'
when rtrim(to_char(loopTime, 'hh24')) between '12' and '16' then 'Afternoon'
when rtrim(to_char(loopTime, 'hh24')) between '17' and '20' then 'Evening'
else 'Night' end || '''' ||
')';
loopTime := loopTime + interval '1 minute';
end loop;
RETURN strresult;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION builddatedim(date, date) OWNER TO gpadmin;