Calculate closest working day in Postgres

Question

I need to schedule some items in a postgres query based on a requested delivery date for an order. So for example, the order has a requested delivery on a Monday (20120319 for example), and the order needs to be prepared on the prior working day (20120316).

Thoughts on the most direct method? I'm open to adding a dates table. I'm thinking there's got to be a better way than a long set of case statements using: SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');

Answer 1

This gets you previous business day.

SELECT 
    CASE (EXTRACT(ISODOW FROM current_date)::integer) % 7
        WHEN 1 THEN current_date-3
        WHEN 0 THEN current_date-2
        ELSE current_date-1
    END AS previous_business_day

Answer 2

To have the previous work day:

select max(s.a) as work_day
from (
    select s.a::date
    from generate_series('2012-01-02'::date, '2050-12-31', '1 day') s(a)
    where extract(dow from s.a) between 1 and 5
    except
    select holiday_date
    from holiday_table
    ) s
where s.a < '2012-03-19'
;

If you want the next work day just invert the query.

Answer 3

SELECT y.d AS prep_day
FROM  (
    SELECT generate_series(dday - 8, dday - 1, interval '1d')::date AS d
    FROM (SELECT '2012-03-19'::date AS dday) x
    ) y
LEFT   JOIN holiday h USING (d)
WHERE  h.d IS NULL
AND    extract(isodow from y.d) < 6
ORDER  BY y.d DESC
LIMIT  1;

• It should be faster to generate only as many days as necessary. I generate one week prior to the delivery. That should cover all possibilities.

• isodow as extract parameter is more convenient than dow to test for workdays.

• min() / max(), ORDER BY / LIMIT 1, that's a matter of taste with the few rows in my query.

• To get several candidate days in descending order, not just the top pick, change the LIMIT 1.

• I put the dday (delivery day) in a subquery so you only have to input it once. You can enter any date or timestamp literal. It is cast to date either way.