Vertica Quick Tip: Add Days to a Date, excluding SAT and SUN

This blog post was authored by Jim Knicely.

Say I want to add 12 days to today’s date April, 12, 2018. That’s easy using date arithmetic. dbadmin=> SELECT '04-12-2018'::DATE + 12 AS today_plus_12_business_days; today_plus_12_days -------------------- 2018-04-24 (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-12-2018' dbadmin(> LIMIT 12 dbadmin(> ) foo2; today_plus_12_business_days ------------ 2018-04-30 (1 row) Have fun!