Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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.