The INTERVAL Function

[JimKnicely]

An Interval in Vertica measures the difference between two points in time.

We can use the INTERVAL function to add or subtract an interval of time to or from a date:

Code: Select all

dbadmin=> SELECT SYSDATE                            "Right Now!",
dbadmin->        SYSDATE + INTERVAL '1 DAY 2 HOURS' "Right Now! + 26 hours",
dbadmin->        SYSDATE - INTERVAL '1 DECADE'      "One decade ago",
dbadmin->        SYSDATE + INTERVAL '3 YEARS 4 MONTHS 3 HOURS 2 MINUTES 33 SECONDS' "When I'll be starting that new diet";
         Right Now!         |   Right Now! + 26 hours    |       One decade ago       | When I'll be starting that new diet
----------------------------+----------------------------+----------------------------+-------------------------------------
2012-07-18 15:09:50.754269 | 2012-07-19 17:09:50.754269 | 2002-07-21 15:09:50.754269 | 2015-11-15 18:12:23.754269
(1 row)

We can also use the INTERVAL function to convert one interval to another:

Code: Select all

dbadmin=> \x
Expanded display is on.
dbadmin=> SELECT INTERVAL '1 DAY' HOUR          "Hours in a Day",
dbadmin->        INTERVAL '1 DAY' MINUTE        "Minutes in a Day",
dbadmin->        INTERVAL '1 DAY' SECOND        "Seconds in a Day",
dbadmin->        INTERVAL '1 MILLENNIUM' HOUR   "Hours in a Millennium",
dbadmin->        INTERVAL '1 MILLENNIUM' MINUTE "Minutes in a Millennium",
dbadmin->        INTERVAL '1 MILLENNIUM' SECOND "Seconds in a Millennium (< current U.S. National Debt)";
-[ RECORD 1 ]------------------------------------------+------------
Hours in a Day                                         | 24
Minutes in a Day                                       | 1440
Seconds in a Day                                       | 86400
Hours in a Millennium                                  | 8760000
Minutes in a Millennium                                | 525600000
Seconds in a Millennium (< current U.S. National Debt) | 31536000000

Have fun!