SQL 在 Impala 中将 YYYYMMDD 字符串转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33024309/
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
Convert YYYYMMDD String to Date in Impala
提问by nxl4
I'm using SQL in Impala to write this query. I'm trying to convert a date string, stored in YYYYMMDD format, into a date format for the purposes of running a query like this:
我在 Impala 中使用 SQL 来编写这个查询。我正在尝试将以 YYYYMMDD 格式存储的日期字符串转换为日期格式，以便运行这样的查询：
SELECT datadate,
session_info
FROM database
WHERE datadate >= NOW() - INTERVAL 5 DAY
ORDER BY datadate DESC;
Since the >= NOW() - INTERVAL 5 DAYcode won't work with the YYYYMMDD string, I'd like to find a way to convert that into a date format that will work with this type of query. My thought is that it should look something like this (based on similar questions about other SQL query editors), but it's not working in Impala:
由于>= NOW() - INTERVAL 5 DAY代码不适用于 YYYYMMDD 字符串，我想找到一种方法将其转换为适用于此类查询的日期格式。我的想法是它应该看起来像这样（基于关于其他 SQL 查询编辑器的类似问题），但它在 Impala 中不起作用：
SELECT datadate,
session_info,
convert(datetime, '20141008', 102) AS session_date
FROM database
WHERE session_date >= NOW() - INTERVAL 5 DAY
ORDER BY session_date DESC;
Anyone know how to do this in Impala?
有人知道如何在 Impala 中做到这一点吗？
EDIT:
编辑：
I finally found a working solution to the problem. None of the attempts using configurations of CASTor CONVERTwould work in Impala, but the below query solves the problem and is fully operational, allowing date math to be performed on a column containing string values:
我终于找到了解决问题的有效方法。使用CAST或配置的尝试CONVERT都不能在 Impala 中工作，但下面的查询解决了这个问题并且是完全可操作的，允许在包含字符串值的列上执行日期数学：
SELECT datadate,
session_info
FROM database
WHERE datadate >= from_unixtime(unix_timestamp(now() - interval 5 days), 'yyyyMMdd')
GROUP BY datadate
ORDER BY datadate DESC;
回答by Turophile
See Timestamp Literalson http://www.cloudera.com/content/cloudera/en/documentation/cloudera-impala/latest/topics/impala_literals.html
You need to add the dashes to your string so Impala will be able to convert it into a date/timestamp. You can do that with something like:
您需要将破折号添加到您的字符串中，以便 Impala 能够将其转换为日期/时间戳。你可以这样做：
concat_ws('-', substr(datadate,1,4), substr(datadate,5,2), substr(datadate,7) )
which you can use instead of datadatein your expression.
您可以datadate在表达式中使用它代替。
回答by user3331901
Native way:
原生方式：
to_timestamp(cast(date_number AS STRING), 'yyyyMMdd')
