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
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)
Have fun!