Add or Subtract Years to or from a Date

[JimKnicely]

A user asked me if there is an ADD_YEARS built in function in Vertica. Unfortunately this function does not in Vertica, but there are alternative function that can be substituted to obtain the result the user desired.

In Vertica we can use the ADD_MONTHS or TIMESTAMPADD functions.

Example:

Code: Select all

dbadmin=> SELECT ADD_MONTHS(DATE '2012-06-06', 12)   add_1_year
dbadmin->      , ADD_MONTHS(DATE '2012-06-06', -12)  sub_1_year
dbadmin->      , TIMESTAMPADD(YEAR, 1, DATE '2012-06-06') add_1_year
dbadmin->      , TIMESTAMPADD(YEAR, -1, DATE '2012-06-06') sub_1_year;
add_1_year | sub_1_year |     add_1_year      |     sub_1_year
------------+------------+---------------------+---------------------
2013-06-06 | 2011-06-06 | 2013-06-06 00:00:00 | 2011-06-06 00:00:00

Vertica also lets us add and subtract INTERVALS via the addition (+) and subtractions (-) operands. However, it’s a good idea to use the built in functions mentioned in the previous example instead because these functions are designed to compensate for leap years.

For instance, it may be confusing at first why the results of the first two columns of the following SELECT statement do not add a full year to the date:

Code: Select all

dbadmin=> SELECT DATE '2011-06-06' + INTERVAL '1 YEAR'    add_1_year_almost
dbadmin->      , DATE '2011-06-06' + 365                  add_1_year_almost
dbadmin->      , DATE '2011-06-06' + INTERVAL '1' YEAR    add_1_full_year
dbadmin->      , ADD_MONTHS(DATE '2011-06-06', 12)        add_1_full_year
dbadmin->      , TIMESTAMPADD(YEAR, 1, DATE '2011-06-06') add_1_full_year;
  add_1_year_almost  | add_1_year_almost |   add_1_full_year   | add_1_full_year |   add_1_full_year
---------------------+-------------------+---------------------+-----------------+---------------------
2012-06-05 00:00:00 | 2012-06-05        | 2012-06-06 00:00:00 | 2012-06-06      | 2012-06-06 00:00:00
(1 row)

Each result is a day shy of a year because the equations in the columns are actually only adding 365 days to the date. Since 2012 is a leap year, there is an addition day being counted.

Have fun!