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!
About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
