question Date Interval in Hive in Support Questions

Date Interval in Hive

souveekray — Wed, 27 Jun 2018 19:18:26 GMT

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

Re: Date Interval in Hive

vmurakami — Thu, 28 Jun 2018 06:23:22 GMT

Hey @Souveek Ray!

What about this?

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

Hope this helps!

Re: Date Interval in Hive

souveekray — Mon, 02 Jul 2018 14:13:31 GMT

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

vmurakami — Mon, 02 Jul 2018 14:17:21 GMT

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 🙂