Date Arithmetic in Vertica
[Deleted User]
Administrator
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)
0