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/
