Date Arithmetic in Vertica

[Deleted User]

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)