Postgresql equivalent of Excel "Networkdays"?
I'm calculating the days between Date_1 and Date_2 and would like to exclude any weekends and holidays (i.e. calculate just business days). I believe my team currently doesn't have a table just for dates or holidays and I was wondering if anyone here could help. Thanks!
Hi Doo Shim - thanks for writing in and apologies for the delayed response.
PostgreSQL does not have a built-in function exactly like Excel's NETWORKDAYS, but you can accomplish this goal in SQL directly:
SELECT count(*) AS count_days_no_weekend FROM generate_series(date '2018-12-01' , date '2018-12-31' , interval '1 day') the_day WHERE extract('ISODOW' FROM the_day) < 6 AND the_day not in ('2018-12-25','2018-12-31')
The above code counts the number of days in December 2018, excluding weekend days and excluding Christmas and New Years Eve. (holidays). A few notes about this:
- The EXTRACT function with 'ISODOW' will return 1-5 for M-F, so excluding 6 and 7 excludes Saturday and Sunday
- You can have as many items as you want in the list of holidays to exclude (or even a subquery from another part of your database that returns a list of dates that are holidays). However, you'll need to store them somewhere as the database has no way of knowing what you do or don't consider a holiday.
Hope that helps - please do reach out to our success team via in-product chat or firstname.lastname@example.org if we can be of further assistance. Thanks!Reply