CODEWITHSUNDEEP
sqlhadoophive
Voystin
Voystin

Reputation: 304

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

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

Upvotes: 11

Views: 93056

Answers (6)

ZeroDecibels
ZeroDecibels

Reputation: 135

If Hive version does not support date_sub, you can hack date_add and pass -1 as the interval parameter.

select current_date as curr_date , date_add(current_date,-1) curr_minus_1;

curr_date  |  curr_minus_1  
2020-03-03 |  2020-03-02

Upvotes: 1

Carbon
Carbon

Reputation: 333

For versions >= Hive 2.0 Try this:

select current_date;

Then try this:

select date_sub(current_date, 1);

It should give you current date minus 1 day.

Upvotes: 6

Raghunatha Reddy V
Raghunatha Reddy V

Reputation: 1

cast(date_sub(CURRENT_DATE, 1) as string)

Upvotes: 0

loneStar
loneStar

Reputation: 4010

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/

Upvotes: 25

Sanjiv
Sanjiv

Reputation: 1815

DATE_SUB is Available in HIVE 2.1.0

date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'.

Prior to Hive 2.1.0 (HIVE-13248) the return type was a String because no Date type existed when the method was created.

Upvotes: 9

Rahul Sharma
Rahul Sharma

Reputation: 5824

The problem is the way you trying to subtract a day from date.I would suggest to subtract number of seconds in a day(86400) from unix timestamp in where clause-

CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))as date)

Upvotes: 8

Related Questions