Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Date Interval in Hive

Solved Go to solution

Date Interval in Hive

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Date Interval in Hive

Hey @Souveek Ray!

What about this?

select date_add(add_months(current_timestamp,-1),-9);
Hope this helps!
3 REPLIES 3

Re: Date Interval in Hive

Hey @Souveek Ray!

What about this?

select date_add(add_months(current_timestamp,-1),-9);
Hope this helps!
Highlighted

Re: Date Interval in Hive

New Contributor

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

Re: Date Interval in Hive

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 :)

Don't have an account?
Coming from Hortonworks? Activate your account here