Re: Work hours?

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