Thread: Work hours?
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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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.
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:
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.
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.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 excludedThese 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.
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> 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>:
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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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?"
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