[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Work hours?
From:       Steven Lembark <lembark () wrkhors ! com>
Date:       2019-08-30 13:26:31
Message-ID: 20190830082631.6a039efc.lembark () wrkhors ! com
[Download RAW message or body]


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

Assumes all weekdays are work days and that all weeks are uniform
for work. Any holiday, emergency, outage leaves you with a skewed
result.

First workaround is table of non-work days: generate a temp table of 
the series not intersecting the non-workdays (materialized views
are nice for this). 

Then you get into issues of different people having different non-
work days, leaving your subtraction table keyed by person+date.

Frequently the non-work days are by employee class, which allows a
table of days off by employee grade + employees w/ grade => days
off by empoloyee.

Then individual employees will have their own time off due to paid
vacation, medical or family leave, and sick days. Depending on your
number of employees a non_working_days w/ date + employee works or 
you get into the pay grade + generic days and employee + pay grade
for the generic days off merged with a separate table of individual
days off. Subtract that from a temp table generated by the sequences
and you'll have a complete schedule.



-- 
Steven Lembark                                     3920 10th Ave South
Workhorse Computing                               Birmingham, AL 35222
lembark@wrkhors.com                                    +1 888 359 3508


[prev in list] [next in list] [prev in thread] [next in thread]