Re: Work hours?

On Tue, Aug 27, 2019, 6:27 PM stan <stanb(at)panix(dot)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.

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.