How do you get dynamic 12 business days in Postgresql?
Here is the code I have that gives me the last 12 days
SELECT *
FROM table
WHERE analysis_date >= current_date - interval '12' day;
analy_date is the date column in the table. I understand why this doesn't work because it doesn't account for workdays. How can I rewrite this to get the last 12 business days span?
I tried searching the internet and found
extract(dow from (date))
But I couldn't find an example where I need an interval on weekdays. Any help would be greatly appreciated.
source to share
This can be solved with CTE:
WITH business_days_back AS (
WITH RECURSIVE bd(back_day, go_back) AS (
-- Go back to the previous Monday, allowing for current_date in the weekend
SELECT CASE extract(dow from current_date)
WHEN 0 THEN current_date - 6
WHEN 6 THEN current_date - 5
ELSE current_date - extract(dow from current_date)::int + 1
END,
CASE extract(dow from current_date)
WHEN 0 THEN 7
WHEN 6 THEN 7
ELSE 12 - extract(dow from current_date)::int + 1
END
UNION
-- Go back by the week until go_back = 0
SELECT CASE
WHEN go_back >= 5 THEN back_day - 7
WHEN go_back > 0 THEN back_day - 2 - go_back
END,
CASE
WHEN go_back >= 5 THEN go_back - 5
WHEN go_back > 0 THEN 0
END
FROM bd
)
SELECT back_day FROM bd WHERE go_back = 0
)
SELECT * FROM my_table WHERE analysis_date >= (SELECT * FROM business_days_back);
Some explanation:
- The internal CTE is triggered by going back to the previous Monday, offsetting
current_date
that falls on a weekend. - The recursive term adds strings, returning full weeks (
back_day - 7
for calendar dates andgo_back - 5
for weekdays) untilgo_back = 0
. - The outer CTE returns the date
back_day
wherego_back = 0
. Therefore, this is a scalar query and you can use it as a subquery in a filter expression.
You can change the number of business days to look back by simply changing the numbers 12
and 7
in the starting SELECT
in the internal CTE. However, keep in mind that the value must be such that it falls back to the previous Monday, or the query fails due to the same initial SELECT
internal CTE.
A more flexible (and probably faster) solution is to use the following function:
CREATE FUNCTION business_days_diff(from_date date, diff int) RETURNS date AS $$
-- This function assumes Mon-Fri business days
DECLARE
start_dow int;
calc_date date;
curr_diff int;
weekend int;
BEGIN
-- If no diff requested, return the from_date. This may be a non-business day.
IF diff = 0 THEN
RETURN from_date;
END IF;
start_dow := extract(dow from from_date)::int;
calc_date := from_date;
IF diff < 0 THEN -- working backwards
weekend := -2;
IF start_dow = 0 THEN -- Fudge initial Sunday to the previous Saturday
calc_date := calc_date - 1;
start_dow := 6;
END IF;
IF start_dow + diff >= 1 THEN -- Stay in this week
RETURN calc_date + diff;
ELSE -- Work back to Monday
calc_date := calc_date - start_dow + 1;
curr_diff := diff + start_dow - 1;
END IF;
ELSE -- Working forwards
weekend := 2;
IF start_dow = 6 THEN -- Fudge initial Saturday to the following Sunday
calc_date := calc_date + 1;
start_dow := 0;
END IF;
IF start_dow + diff <= 5 THEN -- Stay in this week
RETURN calc_date + diff;
ELSE -- Work forwards to Friday
calc_date := calc_date + 5 - start_dow;
curr_diff := diff - 5 + start_dow;
END IF;
END IF;
-- Move backwards or forwards by full weeks
calc_date := calc_date + (curr_diff / 5) * 7;
-- Process any remaining days, include weekend
IF curr_diff % 5 != 0 THEN
RETURN calc_date + curr_diff % 5 + weekend;
ELSE
RETURN calc_date;
END IF;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
This function can take any date to calculate and any number of days in the future (positive value diff
) or past (negative value diff
), including differences during the current week. And since it returns the workday date as a scalar, the usage in your query is very simple:
SELECT *
FROM table
WHERE analysis_date >= business_days_diff(current_date, -12);
Besides, you can also transfer fields from your table and do funky things like:
SELECT t1.some_value - t2.some_value AS value_diff
FROM table t1
JOIN table t2 ON t2.analysis_date = business_days_diff(t1.analysis_date, -12);
i.e. self-consolidation in a certain number of working days.
Please note that this feature assumes a week with a working day of Monday through Friday.
* This function only performs simple arithmetic on scalar values. The CTE has to set up all sorts of structures to support iteration and resultant recordsets.
source to share