Rob Sargent wrote:
> On 8/27/19 4:59 PM, Adrian Klaver wrote:
> > On 8/27/19 3:27 PM, stan 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?
> >
> > Use generate_series:
> >
> > https://www.postgresql.org/docs/11/functions-srf.html
> >
> > to generate all the days in the month.
> >
> > Loop over the days and use EXTRACT:
> >
> > https://www.postgresql.org/docs/11/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
> >
> >
> > to find the dates with a dow(The day of the week as Sunday (0) to
> > Saturday (6)) or isodow(The day of the week as Monday (1) to Sunday (7))
> > that falls in Mon-Fri and add to counter.
> >
> > >
> Don't you also need a feed from something like google US holidays (assuming
> OP is stateside)
The definition of "work hours" differs depending on who
it's being calculated for. If your definition above is
sufficient for your needs then ignore the following but
some people work an 8 hour day, others a 7.6 hour day,
etc. It varies from one agreement to another. Some
people work 7 days a week with several weeks "on" and
several weeks "off". Some are full-time. Others are
part-time. Some might have regular hours. Others might
have an arbitrary roster that changes from week to
week. Some public holidays are state-wide. Others are
regional so you need to know where they work and the
definitions of the regions. Some public holidays aren't
even for the whole day. And no doubt every country is
different.
All of that is by far the biggest component of such a
calculation. The postgres-specific bit is easy and yes,
what Adrian suggests will be fine if you can use the
dates returned by generate_series to look up the
working conditions of the person involved. I've done it
in plpgsql with a loop over the dates rather using
generate_series in plain sql. Both are fine but plain
sql is probablby faster. Do whatever is most readable.
cheers,
raf