From 2ab815157ed6284fc2992d3fd46aa1e66f05d387 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 4 Jan 2006 20:41:25 +0000 Subject: [PATCH 1/1] added static time and date tables git-svn-id: svn://svn.open-ils.org/ILS/trunk@2606 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/reporter/config.sql | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) diff --git a/Open-ILS/src/reporter/config.sql b/Open-ILS/src/reporter/config.sql index dabff185ca..49526e3597 100644 --- a/Open-ILS/src/reporter/config.sql +++ b/Open-ILS/src/reporter/config.sql @@ -18,6 +18,31 @@ CREATE OR REPLACE VIEW reporter.date_hour_series AS FROM reporter.date_series, GENERATE_SERIES(0,23) g(h); + + +CREATE TABLE reporter.date_dim AS + SELECT + EXTRACT('year' FROM date_label)::INT AS year, + EXTRACT('month' FROM date_label)::INT AS month, + EXTRACT('day' FROM date_label)::INT AS day + FROM + (SELECT '1900-01-01'::date + g.x AS date_label + FROM GENERATE_SERIES(0, EXTRACT('days' FROM NOW() + '10 years'::INTERVAL - '1900-01-01'::TIMESTAMP WITH TIME ZONE)::INT) g(x)) as y + ORDER BY 1,2,3; + + +CREATE TABLE reporter.time_dim AS + SELECT + a.x AS hour, + b.x AS minute, + c.x AS second + FROM + GENERATE_SERIES(0, 23) as a(x), + GENERATE_SERIES(0, 59) as b(x), + GENERATE_SERIES(0, 59) as c(x) + order by 1,2,3; + + CREATE TABLE reporter.stage2 ( id serial primary key, stage1 text not null, -- 2.43.2