当前位置: 动力学知识库 > 问答 > 编程问答 >

Impala SQL - How to count Months between timestamps?

问题描述:

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.

网友答案:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: