How many working hours are there in a range of dates?
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 )...