Below are two functions that can be called to build and populate a date or time dimension. 

--- 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;