Question :
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
Answer :
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.