string 在 Impala 中将字符串转换为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36724511/
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 string to timestamp in Impala
提问by Gianluca
How can I convert a stringrepresenting a datetime in the format "YYYY-MM-DD;HH:MM:SS"(i.e. 2016-04-11;19:38:01) to a proper timestamp?
如何将string表示日期时间的格式"YYYY-MM-DD;HH:MM:SS"（即2016-04-11;19:38:01）转换为正确的timestamp？
I thought this would work, but it doesn't.
我以为这会奏效，但事实并非如此。
select
from_unixtime(unix_timestamp(`date`, "YYYY-MM-DD;HH:MM:SS"))
from t1
limit 100;
回答by Aurèle
The return type of from_unixtime()isn't a "proper timestamp" but a string. (And the return type of unix_timestamp()isn't... a timestamp, but a bigint).
的返回类型from_unixtime()不是“正确的timestamp”而是字符串。（并且返回类型unix_timestamp()不是... a timestamp，而是 a bigint）。
Here is what I do to get a timestampreturn type:
这是我为获得timestamp返回类型所做的工作：
select
cast(unix_timestamp(`date`, "yyyy-MM-dd;HH:mm:ss") as timestamp)
from t1
limit 100;
For the sake of completeness, here is how I deal with timezones, locally formatted dates, and storing them as UTC timestamps:
为了完整起见，这里是我如何处理时区、本地格式化日期并将它们存储为 UTC 时间戳：
select
to_utc_timestamp(cast(unix_timestamp(`date`, "yyyy-MM-dd;HH:mm:ss") as timestamp), "Europe/Paris")
from t1
limit 100;
This assumes the -use_local_tz_for_unix_timestamp_conversionsstartup flag is off (this is the default).
这假设-use_local_tz_for_unix_timestamp_conversions启动标志关闭（这是默认设置）。
回答by Gianluca
As suggested by @jbapple in a comment to my question, the issue is with the capitalization. As stated in the Impala documentation
正如@jbapple 在对我的问题的评论中所建议的，问题在于大小写。如 Impala 文档中所述
Currently, the format string is case-sensitive, especially to distinguish m for minutes and M for months. In Impala 1.3 and later, you can switch the order of elements, use alternative separator characters, and use a different number of placeholders for each unit. Adding more instances of y, d, H, and so on produces output strings zero-padded to the requested number of characters. The exception is M for months, where M produces a non-padded value such as 3, MM produces a zero-padded value such as 03, MMM produces an abbreviated month name such as Mar, and sequences of 4 or more M are not allowed. A date string including all fields could be "yyyy-MM-dd HH:mm:ss.SSSSSS", "dd/MM/yyyy HH:mm:ss.SSSSSS", "MMM dd, yyyy HH.mm.ss (SSSSSS)" or other combinations of placeholders and separator characters.
目前，格式字符串是区分大小写的，特别是为了区分 m 代表分钟和 M 代表月份。在 Impala 1.3 及更高版本中，您可以切换元素的顺序、使用替代分隔符以及为每个单元使用不同数量的占位符。添加 y、d、H 等的更多实例会产生用零填充到所请求字符数的输出字符串。例外是 M 表示月份，其中 M 生成非填充值，例如 3，MM 生成零填充值，例如 03，MMM 生成缩写的月份名称，例如 Mar，并且不允许出现 4 个或更多 M 的序列. 包含所有字段的日期字符串可以是“yyyy-MM-dd HH:mm:ss.SSSSSS”、“dd/MM/yyyy HH:mm:ss.SSSSSS”、“MMM dd、yyyy HH.mm.ss (SSSSSS )" 或其他占位符和分隔符的组合。
The right way of writing it is:
正确的写法是：
select
from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd;HH:mm:ss"))
from t1
limit 100;
