SQL 如何将此字符串转换为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47336832/
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 convert this String to Timestamp
提问by phalondon
i have a column with the Data like this "08.06.2017/10:20:46". Data Type is string. i want to convert it into timestamp. i tried CAST("08.06.2017/10:20:46" AS TIMESTAMP) but it doesn't work Can you please help me to convert it?? Thanks
我有一列数据像这样“08.06.2017/10:20:46”。数据类型是字符串。我想将其转换为时间戳。我试过 CAST("08.06.2017/10:20:46" AS TIMESTAMP) 但它不起作用你能帮我转换吗？？谢谢
回答by D Ie
For mysql, there is a function called STR_TO_DATE
对于 mysql，有一个名为STR_TO_DATE的函数
You should call it like this:
你应该这样称呼它：
STR_TO_DATE(string , format)
Take a look at Format specifiers
看看 格式说明符
In your case I'd try with
在你的情况下，我会尝试
STR_TO_DATE('08.06.2017/10:20:46','%d.%m.%Y/%H:%i:%s')
Edit:sorry about the mysql stuff, don't know if I'm supposed to remove it or not... Anyways, for impala, this could get you started:
编辑：抱歉关于 mysql 的东西，不知道我是否应该删除它......无论如何，对于黑斑羚，这可以让你开始：
cast(unix_timestamp('08.06.2017/10:20:46', "dd.MM.yyyy/HH:mm:ss") as timestamp)
The casting is because unix_timestamp function returns a bigint (take a look herefor more information about the impala datetime functions)
强制转换是因为 unix_timestamp 函数返回一个 bigint（查看此处了解有关 impala 日期时间函数的更多信息）
回答by praveen kumar
For Oracle SQL,
对于 Oracle SQL，
use to_date function as below,
使用 to_date 函数如下，
SELECT TO_DATE('08.06.2017/10:20:46','dd.mm.yyyy/hh:mi:ss') FROM DUAL;
SELECT TO_DATE('08.06.2017/10:20:46','dd.mm.yyyy/hh:mi:ss') FROM DUAL;
This conveys that the string is in this(dd.mm.yyyy/hh:mi:ss) form of date and convert to date column...
这表明字符串是日期的 this(dd.mm.yyyy/hh:mi:ss) 形式并转换为日期列...
回答by Ramesh
Hive and Impala deals with dates in a similar manner. You need to use the function unix_timestamp to accept non standard date formats, then use from_unixtime function to convert to a timestamp. Below code should work for your example.
Hive 和 Impala 以类似的方式处理日期。您需要使用函数 unix_timestamp 接受非标准日期格式，然后使用 from_unixtime 函数转换为时间戳。下面的代码应该适用于您的示例。
SELECT from_unixtime(unix_timestamp('08.06.2017/10:20:46','dd.MM.yyyy/hh:mm:ss'));
