How to subtract one day from current date then convert to string in Hive

Question

Here is the case. I'm trying to make select syntax to get data from last day (today we have 21.10 so as a result I should have data with 20.10 date query will be a part of ETL proces in Talend so I can't simply do where date = '2016-10-20') The problem is that all columns in data source are in VARCHAR or STRING type - date also. Source is on Hive Hadoop.

My code:

select 
cast(to_date(from_unixtime(unix_timestamp(dzien ,'yyyyMMdd'), 'yyyy-MM-dd')) as date),
count(ns_utc) as ILOSC_ODSLON
from portal.portal_data 
where
portal_data.opl_ev_ty is null 
and portal_data.opl_ev_as is null
and cast(to_date(from_unixtime(unix_timestamp(dzien ,'yyyyMMdd'), 'yyyy-MM-dd')) as date) = CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))as date) - interval '1' day

GROUP BY 
cast(to_date(from_unixtime(unix_timestamp(dzien ,'yyyyMMdd'), 'yyyy-MM-dd')) as date)

With that code query returns nothing exept columns name. The problem is probably with this part = CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))as date) - interval '1' day.

I made some tests. When I'm running this query

select CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))as date) - interval '1' day

result is 2016-10-20 00:00:00.0 and part 00:00:00.0 probably ruins my query, becasue when in main query instead of = CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))as date) - interval '1' day I'm putting condition = '2016-10-20' result is as expected.

Can you please guide me how to solve this problem?

Instead of Hue I'm using SQL Workbench

Answer 1

once you parsed the date then use date_sub function that is available in hive

date_sub(string startdate, int days) 

date_sub('2008-12-31', 1) = '2008-12-30'

You can even follow the link below.

https://www.qubole.com/resources/cheatsheet/hive-function-cheat-sheet/