On 28/08/2019 15:22, Christopher Browne wrote:
On Tue, Aug 27, 2019, 6:27 PM stan <stanb@xxxxxxxxx
<mailto:stanb@xxxxxxxxx>> 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.
https://medium.com/@duffn/creating-a-date-dimension-table-in-postgresql-af3f8e2941ac
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.
[...]
You don't need a whole table for weekdays. You can easily calculate the
number of weekdays simply from knowing the first day of the month and
how many days in a month.
Cheers,
Gavin
