The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Date Arithmetic in Vertica

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)
Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.