Re: Work hours?

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Work hours?
Date: 2019-08-27 23:12:22
Message-ID: 97292698-e645-0afc-6d5b-10f14bf0275c@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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)
>>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message raf 2019-08-27 23:42:04 Re: Work hours?
Previous Message Paul A Jungwirth 2019-08-27 22:59:55 Re: Work hours?