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.

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Aug 27, 2019 at 3:27 PM stan <stanb@panix.com> wrote:

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.

This gives you all the weekdays in August 2019:

select t::date
from generate_series('2019-08-01'::date, '2019-09-01'::date, interval
'1 day') s(t)
where extract(dow from t) not in (0, 6);

From there you could count & multiply by 8 (e.g. `select count(*) * 8`
instead). You'll probably want to remove holidays first though. If
those lived in another table you could do a NOT EXISTS to remove them
before you count.

Yours,
Paul

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)

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

On Tue, Aug 27, 2019, 6:27 PM stan <stanb@panix.com> 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?

In data warehouse applications, they have the habit of creating tables that
have various interesting attributes about dates.

https://medium.com/@duffn/creating-a-date-dimension-table-in-postgresql-af3f8e2941ac

I'd be inclined to solve this by defining various useful sets of dates; you
might then attach relevant attributes to a dimension table like the d_date
table in the article.

- a table with all weekdays (say, Monday to Friday)

- a table listing statutory holidays that likely need to be excluded

These are going to be small tables even if you put 10 years worth of dates
in it.

Select entries from the first table, excluding holidays from the second,
and you get the set of working days that can then be counted to get the
desired result.

The "data warehouse" approach would probably be to put an extra
is_stat_holiday onto the dimension table; then you could do...

Select count(*) * 8 from d_date where date_actual between '2019-09-01' and
'2019-09-30' and day_of_week in (1,2,3,4,5) and not is_statutory_holiday;

If there are multiple jurisdictions with differing sets of holidays, that's
going to complicate life. Down that road, I'd define a bunch of tables for
differing jurisdictions' holidays, clearly that diverges a bit from the
data warehousing approach.

On 8/27/19 10:22 PM, Christopher Browne wrote:

On Tue, Aug 27, 2019, 6:27 PM stan <stanb@panix.com
<mailto:stanb@panix.com>> 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?

In data warehouse applications, they have the habit of creating tables
that have various interesting attributes about dates.

https://medium.com/@duffn/creating-a-date-dimension-table-in-postgresql-af3f8e2941ac

I'd be inclined to solve this by defining various useful sets of dates;
you might then attach relevant attributes to a dimension table like the
d_date table in the article.

- a table with all weekdays (say, Monday to Friday)

- a table listing statutory holidays that likely need to be excluded

These are going to be small tables even if you put 10 years worth of dates
in it.

We did something similar to that, except all the columns were in one single
table.  It wasn't a data warehouse, though: the RDBMS we used could be
coerced into using a date index when large ranges were needed in detail
tables by joining it to T_CALENDAR, and doing the range filter on T_CALENDAR.

--
Angular momentum makes the world go 'round.

On Aug 27, 2019, at 11:27 PM, stan <stanb@panix.com> 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?

You might find this useful: https://gist.github.com/wttw/b6f5d0d67c31d499c05f22a4f2b6f628

It's not the most efficient approach, but it's relatively simple to customize.

Cheers,
Steve

On Wed, Aug 28, 2019 at 12:27 AM stan <stanb@panix.com> wrote:

Any thoughts as to the best way to approach this?

I've written a couple of functions to compute working hours depending
on a possible per-day hour template.
Another possible implementation besides the other proposed solutions.
<https://github.com/fluca1978/fluca1978-pg-utils/blob/master/examples/functions/working_hours.sql&gt;

Luca

*select sum(case when extract(dow from t.d) in (1,2,3,4,5) then 1 else 0
end) * 8 as hours*
* from generate_series(current_date::date, (current_date + '10
days'::interval), '1 day'::interval) as t(d)*

*This calculates the working days/hours between 2 dates. You can make your
firt/lastr day of the month/year to a date and feed it into the series.*

*Bye Uwe*

Am Mi., 28. Aug. 2019 um 00:27 Uhr schrieb stan <stanb@panix.com>:

On Wed, 28 Aug 2019 at 01:02, Ron <ronljohnsonjr@gmail.com> wrote:

We did something similar to that, except all the columns were in one
single table. It wasn't a data warehouse, though: the RDBMS we used could
be coerced into using a date index when large ranges were needed in detail
tables by joining it to T_CALENDAR, and doing the range filter on
T_CALENDAR.

Ah, interesting! I like it, mostly...

The one bad thing would be that this sorta mis-matches timestamp with
timezone which is a more or less continuous data type (rather than
discrete, like date). I could see an argument, in that environment, to put
a DATE type onto detail tables if they are inevitably being joined to
T_CALENDAR.

I recall we had a case where some reports were ridiculously inefficient
because a query involved effectively a "where date_part(something, column)"
clause that made that into a Seq Scan.

Alternatively (and I'm thinking out loud here), I wonder if putting a range
type with a pair of timestamps would help with matching, as the range type
would put the full range of each day into the table; you could have full
date/time stamps match the calendar table via the range type...

select [stuff] from tz_table t, t_calendar tc
where
[various stuff]
and
tc.t_workday and
tc.t_date between '2017-01-01' and '2017-02-01'
and (to get the range bit)
t.original_tstz <@ tc.t_range;
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On 28/08/2019 15:22, Christopher Browne wrote:

On Tue, Aug 27, 2019, 6:27 PM stan <stanb@panix.com
<mailto:stanb@panix.com>> 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?

In data warehouse applications, they have the habit of creating tables
that have various interesting attributes about dates.

https://medium.com/@duffn/creating-a-date-dimension-table-in-postgresql-af3f8e2941ac

I'd be inclined to solve this by defining various useful sets of
dates; you might then attach relevant attributes to a dimension table
like the d_date table in the article.

- a table with all weekdays (say, Monday to Friday)

- a table listing statutory holidays that likely need to be excluded

These are going to be small tables even if you put 10 years worth of
dates in it.

[...]

You don't need a whole table for weekdays.  You can easily calculate the
number of weekdays simply from knowing the first day of the month and
how many days in a month.

Cheers,
Gavin

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