Get Months Between Two Dates – Hive & Impala

A date can be analyzed in a variety of ways. Once you get the date data for a particular transaction, you’ll undoubtedly want to see how many transactions there are every day, week, or month. You’d look at which days/weeks/months you had a lot of transactions. You’d also look at the days, weeks, and months between such events. This post will show you how to use Apache Hive and Apache Impala to calculate months between two dates.

Apache Hive:
SELECT MONTHS_BETWEEN(‘2021-02-28 10:30:00’, ‘2020-10-30’)
This returns 3.94959677

SELECT MONTHS_BETWEEN (‘2021-02-02′,’2021-01-01’)
This returns 1.03225806

SELECT MONTHS_BETWEEN (‘2021-01-01’, ‘2021-03-14’)

This returns -2.41935483

SELECT MONTHS_BETWEEN (‘2021-02-02’, ‘2021-01-02’)

This returns 1

Apache Impala:
SELECT INT_MONTHS_BETWEEN(‘2021-10-05’, ‘2021-12-01’);
The above query returned 1. 
Ensure that your supplied dates do not fall below a month. If this is the case, it will return 0.

SELECT INT_MONTHS_BETWEEN(‘2021-11-05’, ‘2021-12-01’);
The above query returned 0 because its not a month between the provided dates. 

Hope you find this article helpful.

Do follow for more interesting updates.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s