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.
Select entries from the first table, excluding holidays from the second, and you get the set of working days that can then be counted to get the desired result.
The "data warehouse" approach would probably be to put an extra is_stat_holiday onto the dimension table; then you could do...
Select count(*) * 8 from d_date where date_actual between '2019-09-01' and '2019-09-30' and day_of_week in (1,2,3,4,5) and not is_statutory_holiday;
If there are multiple jurisdictions with differing sets of holidays, that's going to complicate life. Down that road, I'd define a bunch of tables for differing jurisdictions' holidays, clearly that diverges a bit from the data warehousing approach.