Date arithmetic in Vertica is extremely easy!
Example:
What is today’s, yesterday’s and tomorrow’s date?
dbadmin=> SELECT SYSDATE Today,
dbadmin-> SYSDATE - 1 Yesterday,
dbadmin-> SYSDATE + 1 Tomorrow;
Today | Yesterday | Tomorrow
----------------------------+----------------------------+----------------------------
2018-01-18 11:36:43.132482 | 2018-01-17 11:36:43.132482 | 2018-01-19 11:36:43.132482
(1 row)
But you’re not limited to whole days! You can also easily add and subtract partial days (i.e. hours, minutes and seconds)!
dbadmin=> SELECT SYSDATE Today,
dbadmin-> SYSDATE + 5/24 Today_Plus_5_hrs,
dbadmin-> SYSDATE + 5/24/60 Today_Plus_5_mins,
dbadmin-> SYSDATE + 5/24/60/60 Today_Plus_5_secs;
Today | Today_Plus_5_hrs | Today_Plus_5_mins | Today_Plus_5_secs
---------------------------+---------------------------+---------------------------+---------------------------
2018-01-18 11:55:55.06799 | 2018-01-18 16:55:55.06799 | 2018-01-18 12:00:55.06799 | 2018-01-18 11:56:00.06799
(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!
