SQL 如何从当前日期减去一天然后在 Hive 中转换为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40177849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to subtract one day from current date then convert to string in Hive
提问by Voystin
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.
这是这种情况。我正在尝试使用 select 语法来获取最后一天的数据（今天我们有 21.10 因此我应该有 20.10 日期查询的数据将成为 Talend 中 ETL 过程的一部分，所以我不能简单地做where date = '2016-10-20'） 问题是数据源中的所有列都是 VARCHAR 或 STRING 类型 - 日期也是。源在 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.
使用该代码查询不返回任何列名。问题可能出在这部分= 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.0and 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' dayI'm putting condition = '2016-10-20'result is as expected.
结果是2016-10-20 00:00:00.0，部分 00:00:00.0 可能会破坏我的查询，因为在主查询中而不是= CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()))as date) - interval '1' day我放置条件时，= '2016-10-20'结果符合预期。
Can you please guide me how to solve this problem?
你能指导我如何解决这个问题吗？
Instead of Hue I'm using SQL Workbench
我使用 SQL Workbench 而不是 Hue
采纳答案by Rahul Sharma
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-
问题是您尝试从日期中减去一天的方式。我建议从 where 子句中的 unix 时间戳中减去一天中的秒数（86400）-
CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))as date)
回答by loneStar
once you parsed the date then use date_subfunction that is available in hive
解析日期后，请使用date_subhive 中可用的函数
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/
https://www.qubole.com/resources/cheatsheet/hive-function-cheat-sheet/
回答by Sanjiv
DATE_SUB is Available in HIVE 2.1.0
DATE_SUB 在 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'.
减去开始日期的天数：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.
在 Hive 2.1.0 (HIVE-13248) 之前，返回类型是 String，因为创建方法时不存在 Date 类型。
回答by Carbon
For versions >= Hive 2.0 Try this:
对于版本 >= Hive 2.0 试试这个：
select current_date;
Then try this:
然后试试这个：
select date_sub(current_date, 1);
It should give you current date minus 1 day.
它应该给你当前日期减去 1 天。
回答by ZeroDecibels
If Hive version does not support date_sub, you can hack date_addand pass -1as the interval parameter.
如果 Hive 版本不支持date_sub，您可以 hackdate_add并-1作为间隔参数传递。
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
回答by Raghunatha Reddy V
cast(date_sub(CURRENT_DATE, 1) as string)
演员（date_sub（CURRENT_DATE，1）作为字符串）
