4

Using Impala SQL, is there a concise way to count the # of months between two timestamps?

The 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.

misanuk
  • 125
  • 3
  • 9

2 Answers2

4

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 YEAR(date), MONTH(date), and DAY(date) to pull the components out of the date string, and finally use a formula similar to https://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.

Community
  • 1
  • 1
Jeff Hammerbacher
  • 4,226
  • 2
  • 29
  • 36
2

MONTHS_BETWEEN(TIMESTAMP newer, TIMESTAMP older)

Purpose: Returns the number of months between the date portions of two TIMESTAMP values. Can include a fractional part representing extra days in addition to the full months between the dates. The fractional component is computed by dividing the difference in days by 31 (regardless of the month).

Return type: DOUBLE

Added in: CDH 5.5.0 / Impala 2.3.0

Dzamo Norton
  • 1,194
  • 11
  • 17