如何将存储为 bigint 的 Java 时间戳转换为 Presto 中的时间戳? [英] How to convert Java timestamp stored as bigint to timestamp in Presto?

问题描述

我在几天内搜索此内容时运气不佳.

如果我的 hive 表中数据的 avro 架构是:

<代码>{类型":记录"，"name" : "消息",命名空间":com.company.messages"，字段":[{"name" : "时间戳",类型":长"，logicalType":时间戳毫秒"}, {…

我使用 presto 来查询这个，我没有得到格式化的时间戳.

选择"timestamp", typeof("timestamp") 作为类型，current_timestamp 为current_timestamp"，typeof(current_timestamp) 为 current_type从 db.messages 限制 1

<块引用>

时间戳类型 current_timestamp current_type1497210701839 bigint 2017-06-14 09:32:43.098 带时区的亚洲/首尔时间戳

我认为将它们转换为毫秒精度的时间戳不是问题，但我发现我没有明确的方法来做到这一点.

select cast("timestamp" as timestamp) from db.messages limit 1

<块引用>

line 1:16: 不能将 bigint 转换为时间戳

此外，他们还更改了 presto 的时间戳转换，以始终假设源以秒为单位.https://issues.apache.org/jira/browse/HIVE-3454

所以如果我使用 from_unixtime() 我必须砍掉毫秒，否则它会给我一个非常遥远的日期:

 select from_unixtime("timestamp") as "timestamp" from db.messages limit 1

<块引用>

时间戳+49414-08-06 07:15:35.000

当然，经常使用 Presto 的其他人知道如何正确表达转换.(顺便说一句，我无法重新启动 Presto 或 Hive 服务器以将时区强制为 UTC).

解决方案

我没有找到从 Java 时间戳(自 1970 年以来的毫秒数)到时间戳的直接转换，但可以使用 to_unixtime 并添加毫秒作为间隔:

presto>with t as (select cast('1497435766032' as bigint) a)->select from_unixtime(a/1000) + parse_duration(cast((a % 1000) as varchar) || 'ms') from t;_col0-------------------------2017-06-14 12:22:46.032(1 行)

(诚然麻烦，但有效)

I've had little luck searching for this over a couple days.

If my avro schema for data in a hive table is:

{
  "type" : "record",
  "name" : "messages",
  "namespace" : "com.company.messages",
  "fields" : [ {
    "name" : "timeStamp",
    "type" : "long",
    "logicalType" : "timestamp-millis"
  }, {
  …

and I use presto to query this, I do not get formatted timestamps.

select "timestamp", typeof("timestamp") as type,
current_timestamp as "current_timestamp", typeof(current_timestamp) as current_type
from db.messages limit 1

timestamp     type   current_timestamp                  current_type
1497210701839 bigint 2017-06-14 09:32:43.098 Asia/Seoul timestamp with time zone

I thought it would be a non-issue then to convert them to timestamps with millisecond precision, but I'm finding I have no clear way to do that.

select cast("timestamp" as timestamp) from db.messages limit 1

line 1:16: Cannot cast bigint to timestamp

Also they've changed presto's timestamp casting to always assume the source is in seconds. https://issues.apache.org/jira/browse/HIVE-3454

So if I used from_unixtime() I have to chop off the milliseconds or else it gives me a very distant date:

select from_unixtime("timestamp") as "timestamp" from db.messages limit 1

timestamp  
[phone]:15:35.000

Surely someone else who works with Presto more often knows how to express the conversion properly. (I can't restart the Presto nor Hive servers to force the timezone into UTC either btw).

解决方案

I didn't find direct conversion from Java timestamp (number of milliseconds since 1970) to timestamp, but one can be done with to_unixtime and adding milliseconds as interval:

presto> with t as (select cast('1497435766032' as bigint) a)
     -> select from_unixtime(a / 1000) + parse_duration(cast((a % 1000) as varchar) || 'ms') from t;
          _col0          
-------------------------
 2017-06-14 12:22:46.032 
(1 row)

(admittedly cumbersome, but works)

这篇关于如何将存储为 bigint 的 Java 时间戳转换为 Presto 中的时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！