I need a query to extract the 14th day or any other day before a day after excluding Saturday and Sunday in Postgresql.
For Example, I need 14th day before 2020-01-30 after excluding Saturday and Sunday.
Date Dimension is ” YYYY-MM-DD”
I am using ‘ISODOW’ < 6 condition to exclude Sunday and Saturday. But from the count, i am not sure how to fetch the date
extract('ISODOW' FROM the_day) < 6
WITH cand AS ( SELECT DATE '2020-01-30' - i AS d FROM generate_series(1, 2*14+3) AS i ) SELECT d FROM cand WHERE extract(isodow FROM d::timestamp) < 6 ORDER BY d DESC OFFSET 13 LIMIT 1;
The 2*14+3 in the CTE is a safe upper limit and can be improved.