Re: Work hours?

On 8/27/19 10:22 PM, Christopher Browne wrote:
>
>
> On Tue, Aug 27, 2019, 6:27 PM stan <stanb(at)panix(dot)com
> <mailto: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.

We did something similar to that, except all the columns were in one single
table.  It wasn't a data warehouse, though: the RDBMS we used could be
coerced into using a date index when large ranges were needed in detail
tables by joining it to T_CALENDAR, and doing the range filter on T_CALENDAR.

--
Angular momentum makes the world go 'round.