Temporal Questions

I've started a thread on the pgsql-general list that has generated a lot
of heat but little light. Since most of you work daily with postgres in
business applications, perhaps you can illuminate the subject.

The application we're writing depends heavily on temporal checks; that is,
whether actions (such as compliance monitoring) are done -- and entered in
the system -- when they're supposed to be. Some permits require monitoring
once per shift, others daily, weekly, or quarterly. Compliance reports must
be submitted quarterly and annually. And renewals must be initiated well in
advance of expiration dates.

With all this date checking to be done, it seems to me that having a
calendar helper table would make the application easier to code, maintain,
and expand. The calendar would (for each date between the start and end
dates; the latter say 30 years from now) store if it's a weekday, weekend
day, holiday, its Julianized date, and whatever else might be useful. This
would, I believe, facilitate queries such as events due in the next week,
fortnight, or month; test if required monitoring results were entered;
and allow sufficient lead time for report and renewal application
preparation knowing that processing requires at least a specified number of
work days.

Seems to me that business financial applications need such a table, too.
For example, stock trades that must be closed within three days of the
transaction, but which cannot be closed on weekends or holidays.

Are my assumptions about the value of such a table invalid? Do you know of
any such calendar table in PostgreSQL SQL (rather than the MS SQL or Oracle
that I find on the Web)?

Rich

--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863