openpolitics.com

Data Warehouse Time Dimension

The first question is answered by recalling the fundamental reason for wanting dimension tables in a data mart. The dimension tables serve as the source of constraints and as the source of report row headers.


Drop table dim_time;

CREATE TABLE dim_time
( pk_time integer NOT NULL,
time_time time without time zone,
time_hour smallint,
time_minute smallint,
time_second smallint,
time_12hr character(8),
time_24hr character(8),
CONSTRAINT pk_dim_time PRIMARY KEY (pk_time)
)
WITH (
OIDS=FALSE
);
ALTER TABLE dim_time
OWNER TO postgres;

insert into dim_time
(
pk_time
, time_time
, time_hour
, time_minute
, time_second
, time_12hr
, time_24hr
)
select
NULLIF(to_char(time_hour, '00')||
trim(to_char(time_minute, '00'))||
trim(to_char(time_second, '00')), '')::int as pk_time
, time_time
, time_hour
, time_minute
, time_second
, to_char(time_time, 'HH12:MI:SS') as time_12hr
, to_char(time_time, 'HH24:MI:SS') as time_24hr
from
(
select
TIMESTAMP WITHOUT TIME ZONE 'epoch' +
((60*60*time_hour) + (60* time_minute)
+ time_second) * INTERVAL '1 second' as time_time
, time_hour
, time_minute
, time_second

from
generate_series(0,23) as time_hour
, generate_series(0,59) as time_minute
, generate_series(0,59) as time_second
) as t
order by
time_hour,
time_minute,
time_second