Using Impala SQL, is there a concise way to count the # of months between two timestamps?
datediff function only returns the # of days between two timestamps, but I'm hoping there is an elegant way to perform the same calculation on # of months.
Unfortunately there's rarely an elegant answer to this question, even in full-featured programming languages like Python. The answer depends on how you define
# of months between two timestamps.
If I were to solve this problem using Impala built-ins, I'd first sort on the timestamp column, then convert the timestamp to a date string with
TO_DATE(ts), then use
DAY(date) to pull the components out of the date string, and finally use a formula similar to http://stackoverflow.com/a/4040338/171965, with a modification to compare the day number as well and subtract a month if the day number of the later date is lower than the day number of the earlier date.