From: Chad Wagner <chad.wagner@gmail.com>
To: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: extract(field from timestamp) vs date dimension
Date: Tue, 23 Jan 2007 08:24:34 -0500
Message-ID: <81961ff50701230524y14aa883alfe4dab8d1dd5cf10@mail.gmail.com> (raw)
In-Reply-To: <20070123124937.GA13122@oppetid.no>
References: <20070123124937.GA13122@oppetid.no>
On 1/23/07, Tobias Brox <tobias@nordicbet.com> wrote:
>
> Ralph Kimball seems to be some kind of guru on data warehousing, and
> in his books he's strongly recommending to have a date dimension -
> simply a table describing all dates in the system, and having
I would tend to agree with this line of thought.
out from elsewhere - but as for now, I'm mostly only interessted in
> grouping turnover/profit by weeks/months/quarters/years/weekdays. It
> seems so much bloated to store this information, my gut feeling tells it
> should be better to generate them on the fly. Postgres even allows to
> create an index on an expression.
I guess go with your gut, but at some point the expressions are going to be
too complicated to maintain, and inefficient.
Calendar tables are very very common, because traditional date functions
simply can't define business logic (especially things like month end close,
quarter end close, and year end close) that doesn't have any repeating
patterns (every 4th friday, 1st monday in the quarter, etc). Sure you can
stuff it into a function, but it just isn't as maintainable as a table.
--
Chad
http://www.postgresqlforums.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chad.wagner@gmail.com, tobias@nordicbet.com
Subject: Re: extract(field from timestamp) vs date dimension
In-Reply-To: <81961ff50701230524y14aa883alfe4dab8d1dd5cf10@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox