文档说时间戳支持以下转换： $ b
•浮点数值类型：解释为具有小数精度的UNIX时间戳记（秒）
首先，我不确定如何解释这一点。如果我有时间戳2013-01-01 12：00：00.423，我可以将其转换为保留毫秒的数字类型吗？因为这就是我想要的。
更一般地说，我需要在时间戳之间进行比较，比如
select maxts - mints as mytable其中 maxts 和 是时间戳列。目前，这给我 NullPointerException 使用Hive 0.11.0。我能够执行查询，如果我做了类似于
select unix_timestamp（maxts） - unix_timestamp（mints）作为mytable但这只适用于秒，而不是毫秒精度。
任何帮助表示赞赏。告诉我你是否需要额外的信息。
解决方案如果你想使用毫秒，不要使用unix timestamp函数，因为这些考虑日期为自从时代以来的秒数。
hive>描述函数扩展的unix_timestamp; unix_timestamp（[date [，pattern]]） - 返回UNIX时间戳将当前或指定时间转换为1970-01-01以来的秒数。相反，将符合JDBC的时间戳记可以翻倍。 例如： <给出一个制表符分隔的数据：
cat /user/hive/ts/data.txt：a 2013- 01-01 12：00：00.423 2013-01-01 12：00：00.433 b 2013-01-01 12：00：00.423 2013-01-01 12：00：00.733 CREATE EXTERNAL TABLE ts（txt string，st Timestamp，et Timestamp） ROW FORMAT DELIMITED 终止'\ t'的位置LOCATION'/ user / hive / ts';然后你可以以毫秒为单位查询startTime（st）和endTime（et）之间的差异，如下所示：
select txt， cast（ round（ cast（（ es）double）* 1000 ）as int ）延迟 from（select txt，cast（st as double）s，cast（et double）e from ts）q;
The documentation says that timestamps support the following conversion:
•Floating point numeric types: Interpreted as UNIX timestamp in seconds with decimal precision
First of all, I'm not sure how to interpret this. If I have a timestamp 2013-01-01 12:00:00.423, can I convert this to a numeric type that retains the milliseconds? Because that is what I want.
More generally, I need to do comparisons between timestamps such as
select maxts - mints as latency from mytablewhere maxts and mints are timestamp columns. Currently, this gives me NullPointerException using Hive 0.11.0. I am able to perform queries if I do something like
select unix_timestamp(maxts) - unix_timestamp(mints) as latency from mytablebut this only works for seconds, not millisecond precision.
Any help appreciated. Tell me if you need additional information.
解决方案If you want to work with milliseconds, don't use the unix timestamp functions because these consider date as seconds since epoch.
hive> describe function extended unix_timestamp; unix_timestamp([date[, pattern]]) - Returns the UNIX timestamp Converts the current or specified time to number of seconds since 1970-01-01.Instead, convert the JDBC compliant timestamp to double. E.g:
Given a tab delimited data:
cat /user/hive/ts/data.txt : a 2013-01-01 12:00:00.423 2013-01-01 12:00:00.433 b 2013-01-01 12:00:00.423 2013-01-01 12:00:00.733 CREATE EXTERNAL TABLE ts (txt string, st Timestamp, et Timestamp) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/user/hive/ts';Then you may query the difference between startTime(st) and endTime(et) in milliseconds as follows:
select txt, cast( round( cast((e-s) as double) * 1000 ) as int ) latency from (select txt, cast(st as double) s, cast(et as double) e from ts) q;
更多推荐
我如何在配置单元中获得毫秒精度？
发布评论