CODEWITHSUNDEEP
sqldatehivehiveql
user2672739
user2672739

Reputation: 143

How to calculate months between in hive?

Is there a way to calculate months between two dates based on month and year

for eg 2016-01-01 2017-01-22

I need 12 to be returned in integer format in hive.

Upvotes: 3

Views: 24427

Answers (4)

lalit panwar
lalit panwar

Reputation: 96

You can get difference between two dates in month through simple logic

month(DATE(from_utc_timestamp(date2, 'IST'))) - month(DATE(from_utc_timestamp(date1, 'IST')))

Upvotes: 0

leftjoin
leftjoin

Reputation: 38335

months_between UDF is available since Hive 1.2.0

https://issues.apache.org/jira/browse/HIVE-9518

The manual is here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

months_between takes care about days, not only year and month:

hive> select abs(cast(months_between('2016-01-10', '2017-01-10')as int));
OK
12
Time taken: 1.812 seconds, Fetched: 1 row(s)

hive> select abs(cast(months_between('2016-01-10', '2017-01-01')as int));
OK
11
Time taken: 0.084 seconds, Fetched: 1 row(s)

If you want it to calculate exactly based on month and year, use trunc() function:

hive> select abs(cast(months_between(trunc('2016-01-10','MM'), trunc('2017-01-01','MM'))as int));
OK
12
Time taken: 0.123 seconds, Fetched: 1 row(s)

Upvotes: 4

Ravinder Karra
Ravinder Karra

Reputation: 307

Can you done using following ... with day consideration 

select (cast(year('2017-01-22') as int) - cast(year('2016-01-01') as int) )*12 +  (cast(month('2017-01-22') as int) - cast(month('2016-01-01') as int) )  +  IF(day('2017-01-22')  > day('2016-01-01'), 1, 0 )

With out day consideration 

select (cast(year('2017-01-22') as int) - cast(year('2016-01-01') as int) )*12 +  (cast(month('2017-01-22') as int) - cast(month('2016-01-01') as int) )

Upvotes: 0

Pushkr
Pushkr

Reputation: 3619

May be write your own UDF which will do something like -

  1. Covert date(s) to unix time stamps
  2. substract higher time stamp from lower
  3. result is time duration between two dates
  4. divide result by (60 sec * 60 min * 24 hrs * 30 days) to get month

Upvotes: 0

Related Questions