CODEWITHSUNDEEP
sqldatehadoophivehiveql
BigD
BigD

Reputation: 888

How to find previous date in HIVE

I want to find previous date in Hive.

For example :

I have Date as 20180821 (yyyyMMdd) format, I want to find previous date of this in Hive. I tried date_sub() function, seems it works only with yyyy-MM-dd format.

How to solve my issue. Please help

Upvotes: 1

Views: 2920

Answers (3)

leftjoin
leftjoin

Reputation: 38325

In addition to already provided solution with unix_timestamp conversion, please consider also this. The same conversion using substr() looks a little bit longer, but it will work with any dates/timestamps like '19691231 19:00:00' without TZ issues, also this is not slower, because not using SimpleDateFormat/etc and not applying TZ.

date_sub(concat_ws('-', substr('20180821',1,4), substr('20180821',5,2), substr('20180821',7,2)),1)

Upvotes: 1

Rishu S
Rishu S

Reputation: 3968

Possible Duplicate: How to get previous day date in Hive

To answer your question, date_sub will fetch you the previous day record. It works only on your date value in format: yyyy-MM-dd. Syntax of date_sub is as: date_sub(String date, Int days)

Hence you need to first convert your current date format into yyyy-MM-dd format. To achieve this, use the below query:

SELECT from_unixtime(unix_timestamp('20180821','yyyyMMdd'),'yyyy-MM-dd' as converted_date_format;

Next you apply date_sub to the above result set with a 1 day value to get the previous record.

select date_sub(from_unixtime(unix_timestamp('20180821','yyyyMMdd'),'yyyy-MM-dd'),1) as previous_day;

Having said that, if your requirement is to maintain the date format as yyyyMMdd, you can apply regex_replace function to remove the '-', as below:

select regexp_replace(date_sub(from_unixtime(unix_timestamp('20180821','yyyyMMdd'),'yyyy-MM-dd'),1),'-','') as previous_day_formatted_yyyymmdd;

Hope this helps :)

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Convert the format to yyyy-MM-dd using from_unixtime and unix_timestamp on the existing format and then use date_sub.

date_sub(from_unixtime(unix_timestamp('20180821','yyyyMMdd'),'yyyy-MM-dd'),1)

Upvotes: 1

Related Questions