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

Posted April 12, 2018 by Soniya Shah, Information Developer

Three 3D arrows, different colors pointing in different directions
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!