Often in PostgreSQL you may want to count the number of business days between two dates.
You can create the following function named business_days() to do so:
create or replace function count_business(from_date date, to_date date)
returns bigint
as $bd$
select count(d::date) as d
from generate_series(from_date, to_date, '1 day'::interval) d
where extract('dow' from d) not in (0, 6)
$bd$ language sql;
This function will return an integer that represents the number of business days between a starting date and an ending date.
Note that this function uses the ‘dow’ function to extract the day of the week for each day between a starting and ending date and only returns the count of days where the day of the week is not in the values 0 or 6, which correspond to Saturday and Sunday.
The following example shows how to use this function in practice.
Example: How to Count Business Days Between Two Dates in PostgreSQL
Suppose that we create the following table named emp that shows the starting and ending date for various employees at some company:
-- create table named 'emp'
CREATE TABLE emp (
empID INT,
start_date DATE,
end_date DATE
);
-- add rows to the table
INSERT INTO emp VALUES (1, '2024-04-01', '2024-05-14');
INSERT INTO emp VALUES (2, '2022-07-01', '2023-05-14');
INSERT INTO emp VALUES (3, '2022-08-15', '2023-07-17');
INSERT INTO emp VALUES (4, '2023-12-01', '2024-08-01');
INSERT INTO emp VALUES (5, '2024-11-23', '2024-12-01');
-- view all rows in table
SELECT * FROM emp;
empid | start_date | end_date
-------+------------+------------
1 | 2024-04-01 | 2024-05-14
2 | 2022-07-01 | 2023-05-14
3 | 2022-08-15 | 2023-07-17
4 | 2023-12-01 | 2024-08-01
5 | 2024-11-23 | 2024-12-01
Suppose that we would like to count the number of business days between each of the dates in the start_date and end_date columns.
We can create the following function named count_business() to do so:
create or replace function count_business(from_date date, to_date date)
returns bigint
as $bd$
select count(d::date) as d
from generate_series(from_date, to_date, '1 day'::interval) d
where extract('dow' from d) not in (0, 6)
$bd$ language sql;
We can then use this function in a query to count the number of business days between each starting and ending date:
SELECT *,
count_business(start_date, end_date) AS business_days
FROM emp;
empid | start_date | end_date | business_days
-------+------------+------------+---------------
1 | 2024-04-01 | 2024-05-14 | 32
2 | 2022-07-01 | 2023-05-14 | 226
3 | 2022-08-15 | 2023-07-17 | 241
4 | 2023-12-01 | 2024-08-01 | 175
5 | 2024-11-23 | 2024-12-01 | 5
The new column named business_days displays the number of business days between the corresponding dates in the start_date and end_date columns.
For example, we can see that there are 32 business days between 2024-04-01 and 2024-05-14.
Feel free to use this function to count the number of business days between two dates in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Get the Number of Days in a Month
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: Select Rows Where Date is Less Than
