How to subtract months from date in HIVE

Question

I am looking for a method that helps me subtract months from a date in HIVE

I have a date 2015-02-01. Now i need to subtract 2 months from this date so that result should be 2014-12-01.

Can you guys help me out here?

Answer 1

 select add_months('2015-02-01',-2);

if you need to go back to first day of the resulting month:

 select add_months(trunc('2015-02-01','MM'),-2);

Answer 2

--in Hive if the date format is in YYYY-MM-DD format, it becomes really easy to use difference in terms on month and year

from_unixtime(unix_timestamp(2015-02-01,'yyyy-mm-dd')-2*30*24*60*60, 'yyyy-MM-dd');

Answer 3

Please try add_months date function and pass -2 as months. Internally add_months uses Java Calendar.add method, which supports adding or subtracting (by passing negative integer).

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions