Date Interval in Hive

[#1]

Hi,

I am trying to convert a postgres Greenplum function to Hive and encountered the below problem:

cast(from_unixtime(unix_timestamp(current_timestamp - interval 1 Month 9 days),'YYYYMM') as int)

This is working in pyspark when I use a Hive context but the same is failing in Ambari. Can anyone explain this? What should we use instead of interval to reduce 1 month and 9 days? We cant use 39 days as this will sometimes be 39 and sometimes 40

Regards,

Souveek

[#2 · Accepted solution]

Hey @Souveek Ray!

What about this?

select date_add(add_months(current_timestamp,-1),-9);

Hope this helps!

[#3]

Thanks a lot, this worked. but for my requirement I used the below syntax

from_unixtime(unix_timestamp(date_add(add_months(current_timestamp,-1),-9) ,'yyyy-MM-dd'), 'yyyyMM')

Thanks a lot.

Regards,

Souveek

[#4]

Good to know @Souveek Ray !
Please if the issue is solved, I'd kindly ask you to accept as an answer. Doing this will help other users to find the answer and will engage the contributors to keep doing the good job 🙂