Search code examples
sqlpostgresqldate

Calculate closest working day in Postgres

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');

Solution

  • 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.