Vertica Quick Tip: Date Arithmetic

Posted January 18, 2018 by Soniya Shah, Information Developer

Designers discussing project in office sample library
This blog post was authored by Jim Knicely.

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!