Compute day working hours in PL/pgsqlA few days ago there was a very nice thread in the
pgsql-generalmailing list asking for ideas about how to compute working hours in a month.
The idea is quite simple: you must extract the number of working days (let’s say excluding sundays) and multiple each of them for the number of hours per day and then get the sum.
There are a lot of nice and almost one-liner solutions in the thread, so I strongly encourage you to read it all!
I came up with my own solution, that is based on functions, and here I’m going to explain it hoping it can be useful (at least as a starting point).
You can find the code, as usual, on my GitHub repository related to PostgreSQL.
The workhorse functionOne reason I decided to implement the alghoritm using a function was because I want it to be configurable. There are people, like me, that do a job where the working hours are different on a day-by-day basis. So, assuming the more general problem of computing the working hours between two dates, here there’s a possible implementation:
Let’s consider the arguments: the first two are the dates you want to inspect, then there’s a boolean that indicates if saturday is a working day or not. The
CREATE OR REPLACE FUNCTION compute_working_hours( begin_day DATE, end_day DATE, _saturday boolean DEFAULT false, _hour_template real DEFAULT ARRAY[ 8, 8, 8, 8, 8, 8, 8 ]::real, _exclude_days date DEFAULT NULL ) RETURNS real AS $CODE$ DECLARE working_hours real := 0; working_days daterange; current_day date; current_day_hours real; skip boolean; BEGIN -- check arguments IF begin_day IS NULL OR end_day IS NULL OR begin_day >= end_day THEN RAISE EXCEPTION 'Please check dates'; END IF; IF _hour_template IS NULL THEN _hour_template := ARRAY[ 8, 8, 8, 8, 8, 8, 8 ]::real; END IF; WHILE array_length( _hour_template, 1 ) < 7 LOOP _hour_template := array_append( _hour_template, 8 ); END LOOP; -- create the working period date range working_days = daterange( begin_day, end_day, ''); RAISE DEBUG 'Working days in the range %', working_days; current_day := lower( working_days ); LOOP -- skip sundays skip := EXTRACT( dow FROM current_day ) = 0; -- skip saturdays if required skip := skip OR ( NOT _saturday AND EXTRACT( dow FROM current_day ) = 6 ); -- skip this particular day if specified skip := skip OR ( _exclude_days IS NOT NULL AND _exclude_days @> ARRAY[ current_day ] ); IF NOT skip THEN current_day_hours := _hour_template[ EXTRACT( dow FROM current_day ) ]; ELSE current_day_hours := 0; END IF; RAISE DEBUG 'Day % counting % working hours', current_day, current_day_hours; working_hours := working_hours + current_day_hours; current_day := current_day + 1; EXIT WHEN NOT current_day <@ working_days; END LOOP; -- all done RETURN working_hours; END $CODE$ LANGUAGE plpgsql;
_hour_templateis a template with the amount of hours within each day (sunday first, which can be any value since sundays are never working days - at least I would it to be!). Last, an array of days to exclude from the computation (holidays, vacation, and so on).
The function computes a
working_daysdate range including the begin and end date, and then uses a
current_daysingle day date to iterate within the date range. In the main loop, there are checks to skip the current day in the case it is a sunday, or a saturday (and saturdays are not working days) or is included into the array of ecluded days.
Then the tricky part: if the day has to be excluded, the working hours will be zero, otherwise the working hours will be extracted from the hour template. Working hours are then summed together.
Let’s see this in action:
I wish not to work on my beautiful wife’s birthday, so within the three days I’m supposed to work only two and get
testdb=# select compute_working_hours( current_date, current_date + 3, false, NULL, ARRAY[ '2019-08-28' ]::date ); DEBUG: Working days in the range [2019-08-28,2019-09-01) DEBUG: Day 2019-08-28 counting 0 working hours DEBUG: Day 2019-08-29 counting 8 working hours DEBUG: Day 2019-08-30 counting 8 working hours DEBUG: Day 2019-08-31 counting 0 working hours compute_working_hours ----------------------- 16
As you probably have noticed, the hour template is expressed as
realvalues, so that it is possible to express even part of hours, like 8.5 to indicate 8 hours and half. Here probably the usage of
timewould have been a better choice, but with a little complication over the final sum, so I’m not yet convinced about providing such an implementation.
Back to the real problem: computing within a monthHaving the above function in place, it is now possible to overload it and provide a function that computes the working hours in a single month of the year:
As you can see, the function asks for the year and month (as well as other parameters like the hour template), computes the range of dates for the specified month and delegates to the former implementation the computation.
CREATE OR REPLACE FUNCTION compute_working_hours( _year int, _month int, _saturday boolean DEFAULT false, _hour_template real DEFAULT ARRAY[ 8, 8, 8, 8, 8, 8, 8 ]::real, _exclude_days int DEFAULT null ) RETURNS real AS $CODE$ DECLARE _exclude_days_as_dates date; current_index int; BEGIN -- check arguments IF _year IS NULL THEN _year := extract( year FROM CURRENT_DATE ); END IF; IF _month IS NULL THEN _month := extract( month FROM CURRENT_DATE ); END IF; IF _exclude_days IS NOT NULL THEN FOR current_index IN 1 .. array_upper( _exclude_days, 1 ) LOOP _exclude_days_as_dates := array_append( _exclude_days_as_dates, make_date( _year, _month, _exclude_days[ current_index ] ) ); END LOOP; END IF; RETURN compute_working_hours( make_date( _year, _month, 1), ( make_date( _year, _month, 1) + '1 month - 1 day'::interval )::date, _saturday, _hour_template, _exclude_days_as_dates ); END $CODE$ LANGUAGE plpgsql;
One part I’m not really proud of is the
_exclude_daysparameter, that in this version is an array of integers that I have to convert then in array of
dates. On one hand, I wanted the function to have coherent parameters, so if I specify a single month and want to skip the day
28I already know that’s the
28th day of that month, so it is just a noise to ask the user to input a
date. On the other hand, the loop that converts
__exclude_daysinto an array of dates named
_exclude_days_as_datesis less than elegant!
By the way, how is this invoked?
And yes, I love defaults and so pretty much every parameter can be omitted at all and still get a pretty decent result.
testdb=# SELECT compute_working_hours( NULL, NULL, true, NULL, ARRAY[12, 15 ,29] ); DEBUG: Working days in the range [2019-08-01,2019-09-01) DEBUG: Day 2019-08-01 counting 8 working hours DEBUG: Day 2019-08-02 counting 8 working hours DEBUG: Day 2019-08-03 counting 8 working hours ... compute_working_hours ----------------------- 192