On 28/08/2019 15:22, Christopher Browne wrote:
>
>
> On Tue, Aug 27, 2019, 6:27 PM stan <stanb@panix.com
> <mailto:stanb@panix.com>> 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