On 8/27/19 10:22 PM, Christopher Browne wrote:
I am just starting to explore the power of PostgreSQL's time and date
functionality. I must say they seem very powerful.
I need to write a function that, given a month, and a year as input returns
the "work hours" in that month. In other words something like
8 * the count of all days in the range Monday to Friday) within that
calendar month.
Any thoughts as to the best way to approach this?
In data warehouse applications, they have the habit of creating tables that have various interesting attributes about dates.
I'd be inclined to solve this by defining various useful sets of dates; you might then attach relevant attributes to a dimension table like the d_date table in the article.
- a table with all weekdays (say, Monday to Friday)
- a table listing statutory holidays that likely need to be excluded
These are going to be small tables even if you put 10 years worth of dates in it.
We did something similar to that, except all the columns were in one single table. It wasn't a data warehouse, though: the RDBMS we used could be coerced into using a date index when large ranges were needed in detail tables by joining it to T_CALENDAR, and doing the range filter on T_CALENDAR.
--
Angular momentum makes the world go 'round.