Calculating Number of Weekdays between 2 Dates (Redshift and Postgres)

The parameterized snippet below can be used to find the number of weekdays between 2 dates. We named this one difference_in_weekdays(start_date,end_date) 

Redshift

(DATEDIFF('day', [start_date], [end_date]))
  -(DATEDIFF('week',[start_date], [end_date]) * 2)
  -(CASE WHEN DATE_PART(dow, [start_date]) = 0 THEN 1 ELSE 0 END)
  -(CASE WHEN DATE_PART(dow, [end_date]) = 6 THEN 1 ELSE 0 END)

Postgres


DATE_PART('day',[[end_date]:week]::timestamp - [[start_date]:week]::timestamp) -
 (TRUNC(DATE_PART('day', [[end_date]:week]::timestamp - [[start_date]:week]::timestamp )/7) * 2)
  - (CASE WHEN EXTRACT (dow from [start_date]::timestamp) NOT IN (0) THEN EXTRACT (dow from [start_date]::timestamp) - 1 ELSE 5 END)
  + (CASE WHEN EXTRACT (dow from [end_date]::timestamp) NOT IN (0) THEN EXTRACT (dow from [end_date]::timestamp) - 1 ELSE 5 END)

The snippet can then be called in a query as shown below:

select [difference_in_weekdays('2017-06-20','2018-07-16')]

Which returns 279.

Source: Based of the solution here.

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • This is great! I wrote another snippet that can be used in conjunction with this one to get business days between dates. The following query will count how many holidays have been observed from the start of 2018 up to today's date for queries in PST.

    (select count(*) from (
      select
    'New Year''s Day', '2018-01-01'::date
    union all select
    'Martin Luther King, Jr. Day' , '2018-01-15'::date
    union all select
    'Memorial Day', '2018-05-28'
    union all select
    'Independence Day', '2018-07-04'
    union all select
    'Labor Day', '2018-09-03'
    union all select
    'Veteran''s Day', '2018-11-12'
    union all select
    'Thanksgiving Day', '2018-11-22'
    union all select
    'Day after Thanksgiving', '2018-11-23'
    union all select
    'Winter Holiday', '2018-12-25'
      )
    where date <=  [sysdate:pst:date])

    To adjust this for your organization and timezone, simply add or remove rows and update the dates appropriately. Then make sure the timezone formatter at the end is correct. 

    Now the count of business days between two dates in 2018 can be had with:

    difference_in_weekdays(start_date,end_date) - holidays_observed_to_date
    Reply Like 1
reply to topic
Like1 Follow
  • 1 Likes
  • 1 mth agoLast active
  • 1Replies
  • 1107Views
  • 2 Following