1

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

leftjoin
  • 36,950
  • 8
  • 57
  • 116
BigD
  • 850
  • 2
  • 17
  • 40
  • What is the meaning of previous date? Is it `given_date minus 1` or `an immediate previous date that is less than the given_date` which could be `minus 1 or minus 3 or minus 5` etc – Bala Aug 22 '18 at 20:20

3 Answers3

1

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)
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
1

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

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Rishu Shrivastava
  • 3,745
  • 1
  • 20
  • 41
1

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)
leftjoin
  • 36,950
  • 8
  • 57
  • 116