Support Questions
Find answers, ask questions, and share your expertise

Date Interval in Hive

Explorer

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

Hey @Souveek Ray!

What about this?

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

View solution in original post

3 REPLIES 3

Hey @Souveek Ray!

What about this?

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

View solution in original post

Explorer

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

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 🙂