Menu
When using Hive there is the functionality to return the select datediff('date1','date2') from table value which returns the number of days between two dates.
What if I would be interested in the months between the two dates?
One option would be to divide the result by 30.5, as the average months has approximately 30.5 days, but this would certainly yield an inprecision when large date ranges are considered.
Do you know a way of retrieving the number of months (rather than the number of days) in a similar fashion with standard SQL (ideally HIVE) syntax?
423
5.8 months_between(date1, date2) Hive months_between() is used to return the number of months in between two dates.
select (date_add(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd'), 2 - month(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd')) )); This results in 2015-05-30. The results should be like: if Today is '2015-06-03', then the result of last two months should be like: '2015-04-01'.
In Hive, you are able to choose your preferred date format and calendar layout. To set your date preferences, navigate to your profile dropdown > 'My profile' > 'Edit profile'. 'Date Format' will allow you to change from US (MM/DD/YY) to international format (DD/MM/YY).
You can try with:
SELECT CAST(MONTHS_BETWEEN(date1, date2) AS INT) as numberOfMonthsBetweenDates
FROM table
This will return your expected result.
190
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
