string 迄今为止的 Hive 转换字符串 dd-MM-yyyy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32475465/
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
Hive cast string to date dd-MM-yyyy
提问by pele88
How can I cast a string in the format 'dd-MM-yyyy' to a date type also in the format 'dd-MM-yyyy' in Hive?
如何将格式为“dd-MM-yyyy”的字符串转换为 Hive 中格式为“dd-MM-yyyy”的日期类型？
Something along the lines of:
类似的东西：
CAST('12-03-2010' as date 'dd-mm-yyyy')
回答by Ardit
try:
尝试：
from_unixtime(unix_timestamp('12-03-2010' , 'dd-MM-yyyy'))
回答by KeyMaker00
If I have understood it correctly, you are trying to convert a String representing a given date, to another type.
如果我理解正确，您正在尝试将表示给定日期的字符串转换为另一种类型。
Note:(As @Samson Scharfrichter has mentioned)
注意：（正如@Samson Scharfrichter 提到的）
- the default representation of a date is ISO8601
- a date is stored in binary (not as a string)
- 日期的默认表示是 ISO8601
- 日期以二进制形式存储（而不是字符串）
There are a few ways to do it. And you are close to the solution. I would use the CAST (which converts to a DATE_TYPE):
有几种方法可以做到。你已经接近解决方案了。我会使用 CAST（转换为 DATE_TYPE）：
SELECT cast('2018-06-05' as date);
Result: 2018-06-05 DATE_TYPE
结果：2018-06-05 DATE_TYPE
or (depending on your pattern)
或（取决于您的模式）
select cast(to_date(from_unixtime(unix_timestamp('05-06-2018', 'dd-MM-yyyy'))) as date)
Result: 2018-06-05 DATE_TYPE
结果：2018-06-05 DATE_TYPE
And if you decide to convert ISO8601 to a date type:
如果您决定将 ISO8601 转换为日期类型：
select cast(to_date(from_unixtime(unix_timestamp(regexp_replace('2018-06-05T08:02:59Z', 'T',' ')))) as date);
Result: 2018-06-05 DATE_TYPE
结果：2018-06-05 DATE_TYPE
Hive has its own functions, I have written some examples for the sake of illustration of these date- and cast- functions:
Hive 有自己的函数，为了说明这些日期和强制转换函数，我编写了一些示例：
Date and timestamp functions examples:
日期和时间戳函数示例：
Convert String/Timestamp/Date to DATE
将字符串/时间戳/日期转换为日期
SELECT cast(date_format('2018-06-05 15:25:42.23','yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE
SELECT cast(date_format(current_date(),'yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE
SELECT cast(date_format(current_timestamp(),'yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE
Convert String/Timestamp/Date to BIGINT_TYPE
将字符串/时间戳/日期转换为 BIGINT_TYPE
SELECT to_unix_timestamp('2018/06/05 15:25:42.23','yyyy/MM/dd HH:mm:ss'); -- 1528205142 BIGINT_TYPE
SELECT to_unix_timestamp(current_date(),'yyyy/MM/dd HH:mm:ss'); -- 1528205000 BIGINT_TYPE
SELECT to_unix_timestamp(current_timestamp(),'yyyy/MM/dd HH:mm:ss'); -- 1528205142 BIGINT_TYPE
Convert String/Timestamp/Date to STRING
将字符串/时间戳/日期转换为字符串
SELECT date_format('2018-06-05 15:25:42.23','yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE
SELECT date_format(current_timestamp(),'yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE
SELECT date_format(current_date(),'yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE
Convert BIGINT unixtime to STRING
将 BIGINT unixtime 转换为 STRING
SELECT to_date(from_unixtime(unixtime,'yyyy/MM/dd HH:mm:ss')); -- 2018-06-05 STRING_TYPE
Convert String to BIGINTunixtime
将字符串转换为 BIGINTunixtime
SELECT unix_timestamp('2018-06-05 15:25:42.23','yyyy-MM-dd') as TIMESTAMP; -- 1528149600 BIGINT_TYPE
Convert String to TIMESTAMP
将字符串转换为时间戳
SELECT cast(unix_timestamp('2018-06-05 15:25:42.23','yyyy-MM-dd') as TIMESTAMP); -- 1528149600 TIMESTAMP_TYPE
Idempotent (String -> String)
幂等（字符串 -> 字符串）
SELECT from_unixtime(to_unix_timestamp('2018/06/05 15:25:42.23','yyyy/MM/dd HH:mm:ss')); -- 2018-06-05 15:25:42 STRING_TYPE
Idempotent (Date -> Date)
幂等（日期 -> 日期）
SELECT cast(current_date() as date); -- 2018-06-26 DATE_TYPE
Current date / timestamp
当前日期/时间戳
SELECT current_date(); -- 2018-06-26 DATE_TYPE
SELECT current_timestamp(); -- 2018-06-26 14:03:38.285 TIMESTAMP_TYPE
回答by Samson Scharfrichter
AFAIK you must reformat your Stringin ISO format to be able to cast it as a Date:
AFAIK 您必须以 ISO 格式重新格式化您的String才能将其转换为Date：
cast(concat(substr(STR_DMY,7,4), '-',
substr(STR_DMY,1,2), '-',
substr(STR_DMY,4,2)
)
as date
) as DT
To display a Dateas a Stringwith specific format, then it's the other way around, unless you have Hive 1.2+ and can use date_format()
要将日期显示为具有特定格式的字符串，则相反，除非您拥有 Hive 1.2+ 并且可以使用date_format()
=> did you check the documentationby the way?
=> 你有没有检查文档？
回答by Terminator17
Let's say you have a column 'birth_day' in your table which is in string format, you should use the following query to filter using birth_day
假设您的表中有一个字符串格式的列“birth_day”，您应该使用以下查询来过滤使用birth_day
date_Format(birth_day, 'yyyy-MM-dd')
You can use it in a query in the following way
您可以通过以下方式在查询中使用它
select * from yourtable
where
date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';
