Menu
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
709
datediff function in Hive takes 2 dates in String type and gives you the difference between the dates.
Please try add_months date function and pass -2 as months. Internally add_months uses Java Calendar. add method, which supports adding or subtracting (by passing negative integer). Save this answer.
The DATEADD() function takes three arguments: datepart , number , and date . Here, the value of datepart is day , because the unit of time you want to subtract is day. The second argument is -1 (you subtract 1 day, which is the same as adding -1 day).
TO_DATE function in Apache Hive helps in converting the string into DATE format. If the string has date and time, it will convert and return the date part.
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/
148
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
