Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 776BC9FB2BE
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 23 Jan 2007 09:24:44 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 26282-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 23 Jan 2007 09:24:35 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.186])
	by postgresql.org (Postfix) with ESMTP id 6E8C99FB28B
	for <pgsql-performance@postgresql.org>;
	Tue, 23 Jan 2007 09:24:40 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so233999nfb
	for <pgsql-performance@postgresql.org>;
	Tue, 23 Jan 2007 05:24:35 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=plS08NcztL/R1IJgTmmVcrpk9BcbYy0+JgnnYCwNbnG0Ixj6lhgBaMCQCy2pv1nPTgN7zopq4FYNbv8EX1qcjLihUBirR8+7Z+PpqIzr8S7oWqlXwbgbczsTHjsRixn/WnsYTsRU+YKmXe0Rt+9lkUGqN5p7Lx50tBU2W54+CQ4=
Received: by 10.49.92.18 with SMTP id u18mr1068428nfl.1169558674068;
	Tue, 23 Jan 2007 05:24:34 -0800 (PST)
Received: by 10.49.65.8 with HTTP; Tue, 23 Jan 2007 05:24:34 -0800 (PST)
Message-ID: <81961ff50701230524y14aa883alfe4dab8d1dd5cf10@mail.gmail.com>
Date: Tue, 23 Jan 2007 08:24:34 -0500
From: "Chad Wagner" <chad.wagner@gmail.com>
To: "Tobias Brox" <tobias@nordicbet.com>
Subject: Re: extract(field from timestamp) vs date dimension
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20070123124937.GA13122@oppetid.no>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_3048_12748352.1169558674014"
References: <20070123124937.GA13122@oppetid.no>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/347
X-Sequence-Number: 22948

------=_Part_3048_12748352.1169558674014
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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/

------=_Part_3048_12748352.1169558674014
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 1/23/07, <b class="gmail_sendername">Tobias Brox</b> &lt;<a href="mailto:tobias@nordicbet.com">tobias@nordicbet.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Ralph Kimball seems to be some kind of guru on data warehousing, and<br>in his books he&#39;s strongly recommending to have a date dimension -<br>simply a table describing all dates in the system, and having</blockquote><div>
<br>I would tend to agree with this line of thought.<br>&nbsp;</div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">out from elsewhere - but as for now, I&#39;m mostly only interessted in
<br>grouping turnover/profit by weeks/months/quarters/years/weekdays.&nbsp;&nbsp;It<br>seems so much bloated to store this information, my gut feeling tells it<br>should be better to generate them on the fly.&nbsp;&nbsp;Postgres even allows to
<br>create an index on an expression.</blockquote><div><br>I guess go with your gut, but at some point the expressions are going to be too complicated to maintain, and inefficient.<br><br>Calendar tables are very very common, because traditional date functions simply can&#39;t define business logic (especially things like month end close, quarter end close, and year end close) that doesn&#39;t have any repeating patterns (every 4th friday, 1st monday in the quarter, etc).&nbsp; Sure you can stuff it into a function, but it just isn&#39;t as maintainable as a table.
<br></div><br></div><br><br clear="all"><br>-- <br>Chad<br><a href="http://www.postgresqlforums.com/">http://www.postgresqlforums.com/</a>

------=_Part_3048_12748352.1169558674014--