Add Days to a Date, excluding SAT and SUN

[Deleted User]

Jim Knicely authored this tip.

Suppose I want to add 12 days to today’s date April, 16, 2018. That’s easy using date arithmetic.

dbadmin=> SELECT '04-16-2018'::DATE + 12 AS today_plus_12_business_days;
today_plus_12_days
--------------------
2018-04-28
(1 row)

But what if I want to only add 12 “business” days and exclude the “weekend” days? That’s not as easy, but not too difficult thanks to Vertica’s TIMESERIES clause!

dbadmin=> SELECT MAX(the_date) AS today_plus_12_business_days FROM (
dbadmin(> SELECT the_date
dbadmin(>   FROM (SELECT ts::date AS the_date
dbadmin(>           FROM (SELECT '01-JAN-1901'::TIMESTAMP as tm UNION SELECT '31-DEC-2100'::TIMESTAMP as tm) as t
dbadmin(>         TIMESERIES ts as '1 DAY' OVER (ORDER BY tm)) foo
dbadmin(>  WHERE DAYOFWEEK(the_date) NOT IN (1, 7)
dbadmin(>    AND the_date > '04-16-2018'
dbadmin(>  LIMIT 12
dbadmin(> ) foo2;
today_plus_12_business_days
------------
2018-05-02
(1 row)

Have fun!