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