Re: Work hours?

On 8/27/19 5: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?

I haven't tried this, but should point you in the right direction:

SELECT SUM(EXTRACT(DOW FROM the_date)) * 8.0 AS work_week_hours
FROM some_table
WHERE EXTRACT(DOW FROM the_date) BETWEEN 1 and 5
  AND the_date BETWEEN '2019-07-01' AND '2019-07-31 23:59:59';

It'll probably (nay, almost definitely) seq scan.

--
Angular momentum makes the world go 'round.