I am not sure I see why it would be good to do this using SQL, but I do know that I have used a number of Perl packages for this sort of thing. When I have done this in the past, I'd do the date and time calculations in Perl and feed the result to whatever RDBMS I happen to be using (PostgreSQL, MySQL, MS SQL Server 2005). I suppose that if it is nessary to do it within an SQL script, resort could be made to functions that in turm use the Perl packages. But a question: Why would any schema that includes temporal components need a calendar table? I use temporal components all the time and have yet to need a calendar table. In fact, some of my database applications are multitemporal, keeping track of edits to data that correct or update data, so that during an audit script, one can determine what a decision maker knew at the time he made a decision. This is so that a decision that was bad, but based on good data can be distinguished from a decision that had been based on bad data, but which would have been a good decision had the data been correct. The first option warrants correction of the decision maker while the latter warrants examination of the data entry process. I have found my Perl scripts adequate for those few instances where use of my tyemporal data depended on a calendar. I am not arguing with you. I just want to know in what circumstances my schemas can be improved by a calendar table, and how it provides a benefit over my more usual Perl functions. Cheers, Ted
Rich Shepard <[EMAIL PROTECTED]> wrote: On Mon, 30 Apr 2007, Brent Wood wrote: > If I'm following this correctly, then interval & extract timepart can be > used to provide all the required functionality: Thanks, Brent. Your suggestions complete the approach I was considering. There is no need for real-time response, to checking after each shift or day -- or other time period -- will be sufficient. I wonder if a workweek/holiday calendar table for PostgreSQL already exists. If not I need to track down the procedure for creating one as Joe Celko references such a calendar in his books. I think that any schema that has temporal components needs such a table. Rich -- Richard B. Shepard, Ph.D. | The Environmental Permitting Applied Ecosystem Services, Inc. | Accelerator(TM) Voice: 503-667-4517 Fax: 503-667-8863 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
