Postgresql equivalent of Excel "Networkdays"?

Hi all,

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!

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 success@modeanalytics.com if we can be of further assistance. Thanks!

    Reply Like
reply to topic
Like Follow
  • Status Answered
  • 5 days agoLast active
  • 1Replies
  • 124Views
  • 2 Following

Create A New Discussion

Share your thoughts