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

score 9 · Answer 1 · answered Apr 14 '14 at 17:01

9

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

answered Apr 14 '14 at 17:01

Yoseph

608
9
22

Clodoaldo Neto · Accepted Answer · 2012-03-16T20:45:46.637

8

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.

edited Mar 16 '12 at 20:45

answered Mar 16 '12 at 20:28

Clodoaldo Neto

118,695
26
233
260

What if the Friday is a holiday? – mu is too short Mar 16 '12 at 20:42
1

Worked like a charm. Thanks. To clarify one thing for others, when Clodoaldo says "invert the query" its change it to "select min(s.a)", not change the < to >. – Dustin Mar 16 '12 at 20:45
Holidays I'm going to handle slightly differently. I assume to use his solution above you'd have to store them somewhere. But they're infrequent enough for us that we'll just reschedule the orders. – Dustin Mar 16 '12 at 20:46
@Dustin To invert you should do both `min` and `>` – Clodoaldo Neto Mar 16 '12 at 20:48
Is there a `holiday_table` in Postgres that I am missing? – sedeh Mar 04 '17 at 00:01

Erwin Brandstetter · Answer 3 · 2012-03-17T04:34:07.780

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.

score 0 · Answer 4 · answered Mar 17 '12 at 01:08

CREATE TABLE Holidays (Holiday, PrecedingBusinessDay) AS VALUES
  ('2012-12-25'::DATE, '2012-12-24'::DATE),
  ('2012-12-26'::DATE, '2012-12-24'::DATE);
SELECT Day, COALESCE(PrecedingBusinessDay, PrecedingMondayToFriday)
FROM
  (SELECT Day, Day - CASE DATE_PART('DOW', Day)
                         WHEN 0 THEN 2
                         WHEN 1 THEN 3
                         ELSE 1
                     END AS PrecedingMondayToFriday
   FROM TestDays) AS PrecedingMondaysToFridays
LEFT JOIN Holidays ON PrecedingMondayToFriday = Holiday;

You might want to rename some of the identifiers :-).

Calculate closest working day in Postgres

4 Answers4

Linked