Geek Answers Handbook

Calculate your nearest business day at Postgres

I need to schedule some items in a postgres request based on the requested delivery date for the order. So, for example, the order has the requested delivery on Monday (for example, 20120319), and the order must be prepared on the previous business day (20120316).

Thoughts on the most direct method? I am open to adding a date table. I think there should be a better way than a long set of case arguments, using: SELECT EXTRACT (DOW FROM TIMESTAMP '2001-02-16 20:38:40');

+5
source share
4 answers

To have a previous business 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 business day to simply invert the request.

+7
source

.

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
+5
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;

  • , . . .

  • isodow extract , dow .

  • min()/max(), ORDER BY / LIMIT 1, .

  • , , LIMIT 1.

  • I put dday(delivery day) in a subquery, so you only need to enter it once. You can enter any literal dateor timestamp. It is transmitted datein any way.

+4
source 
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 :-).

0
source

More articles:


All Articles