Do we have ADD_YEARS() Function in vertica?

Moderator: NorbertKrupa

Post Reply
rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Do we have ADD_YEARS() Function in vertica?

Post by rajasekhart » Tue Jun 05, 2012 12:19 pm

Hi ,

Do we have ADD_YEARS() Function in vertica?
or
any alternative to add certain number of years to the given date?


Thanks,
Raj
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Do we have ADD_YEARS() Function in vertica?

Post by id10t » Tue Jun 05, 2012 12:44 pm

Hi Raj!

Look at 'INTERVAL' in docs: http://my.vertica.com/docs/CE/5.1.1/HTM ... m#9255.htm

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Do we have ADD_YEARS() Function in vertica?

Post by id10t » Tue Jun 05, 2012 2:35 pm

Or

Code: Select all

CREATE FUNCTION add_years(dt DATE, inc int) RETURN DATE
AS BEGIN
  RETURN ( DATE (YEAR(dt) + inc || '-' || MONTH(dt) || '-' || DAY(dt)) );
END;
Or

Code: Select all

CREATE FUNCTION add_years(dt timestamp, inc int) RETURN timestamp
AS BEGIN 
  RETURN ((YEAR(dt) + inc || '-' || MONTH(dt) || '-' || DAY(dt) || ' ' || dt::TIME)::TIMESTAMP);
END;

Code: Select all

telecom_db=> select add_years('2021-02-28'::timestamp, 8);
      add_years      
---------------------
 2029-02-28 00:00:00
(1 row)

telecom_db=> select add_years('2021-02-28'::date, -21);
 add_years  
------------
 2000-02-28
(1 row)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Do we have ADD_YEARS() Function in vertica?

Post by JimKnicely » Wed Jun 06, 2012 1:51 pm

You can also use the ADD_MONTHS() built in function to add years...

Code: Select all

dbadmin=> SELECT add_months(DATE '2012-06-06', 12)     add_1_year,
dbadmin->        add_months(DATE '2012-06-06', 24)     add_2_years,
dbadmin->        add_months(DATE '2012-06-06', 6)      add_half_year,
dbadmin->        add_months(DATE '2012-06-06', 30)     add_2_and_half_years,
dbadmin->        add_months(DATE '2012-06-06', -12)    subtract_1_year;
 add_1_year | add_2_years | add_half_year | add_2_and_half_years | subtract_1_year
------------+-------------+---------------+----------------------+-----------------
 2013-06-06 | 2014-06-06  | 2012-12-06    | 2014-12-06           | 2011-06-06
(1 row)
Or the TIMESTAMPADD() function...

Code: Select all

dbadmin=> SELECT timestampadd(year, 1, DATE '2012-06-06') add_1_year,
dbadmin->        timestampadd(year, -1, DATE '2012-06-06') subtract_1_year;
     add_1_year      |   subtract_1_year
---------------------+---------------------
 2013-06-06 00:00:00 | 2011-06-06 00:00:00
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Database Development”