# Compute day working hours in PL/pgsql

A few days ago there was a very nice thread in the `pgsql-general` mailing 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 function

One 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:
``````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
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;
``````
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 `_hour_template` is 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_days` date range including the begin and end date, and then uses a `current_day` single 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:
``````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
``````
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 `16` hours.

As you probably have noticed, the hour template is expressed as `real` values, 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 `time` would 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 month

Having 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:
``````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;
``````
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.

One part I’m not really proud of is the `_exclude_days` parameter, that in this version is an array of integers that I have to convert then in array of `date`s. On one hand, I wanted the function to have coherent parameters, so if I specify a single month and want to skip the day `28` I already know that’s the `28`th 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_days` into an array of dates named `_exclude_days_as_dates` is less than elegant!

By the way, how is this invoked?
``````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
``````
And yes, I love defaults and so pretty much every parameter can be omitted at all and still get a pretty decent result.

The article Compute day working hours in PL/pgsql has been posted by Luca Ferrari on August 30, 2019