代码之家 › 专栏 › 技术社区 › earl

在Apache Drill中使用TO_DATE将UNIXTIMESTAMP转换为人类可读的时间格式

apache-drill hadoop2 hdfs

earl · 技术社区 · 7 年前

我的JSON文件是一个时间戳字段,采用UNIX上载时间戳格式,如 1501580484655 01-08-2017 9:41:24.655 调幅

我试图将此时间戳转换为人类可读的时间格式,直到毫秒,但没有成功。

到目前为止,我已经尝试了以下内容:

1- select TO_DATE(1501580484655) from (VALUES(1));

2017-08-01 这在约会之前是可以的,但我想等到毫秒。

select TO_DATE(1501580484655,'yyyy-MM-dd HH:mm:ss.SSS') from (VALUES(1));

订单号为: Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "1501580484655" is malformed at "4655"

3- select TO_DATE(1501580484,'yyyy-MM-dd HH:mm:ss.SSS') from (VALUES(1));

订单号为: Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "1501580484" is malformed at "4"

select TO_DATE(150158048,'yyyy-MM-dd HH:mm:ss.SSS') from (VALUES(1));

订单号为: Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "150158048" is too short

5- SELECT TO_TIMESTAMP(1501580484655) from (VALUES(1));

49553-03-11 18:10:55.0 2017-08-01 09:41:24.0

但当我在json文件的upload timestamp列上运行此查询时,我得到:

5a- select TO_TIMESTAMP(SUBSTR((cast(UploadTimeStamp as INTEGER)),1,10)) from dfs.root. /测试/ limit 5;

订单号为:

`Error: SYSTEM ERROR: SchemaChangeException: Failure while trying to materialize incoming schema.  Errors:

Error in expression at index -1.  Error: Missing function implementation: [castTINYINT(VARCHAR-OPTIONAL)].  Full expression: --UNKNOWN EXPRESSION--..

Fragment 1:3

[Error Id: 7a91a9f8-9776-4f0d-9b9f-da3e65cc282a on rjio-devcluster1-BigdataNode1:31010] (state=,code=0)

要获得订单号,正确的查询应该是什么 2017-08-01 HH:mm:ss.SSS

3 回复 | 直到 7 年前

Vitalii Diravka 7 年前

0: jdbc:drill:zk=local> SELECT TO_TIMESTAMP(1427936330) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-04-02 00:58:50.0  |
+------------------------+
1 row selected (1.612 seconds)
0: jdbc:drill:zk=local> SELECT TO_TIMESTAMP(1427936330.456) FROM (VALUES(1)); 
+--------------------------+
|          EXPR$0          |
+--------------------------+
| 2015-04-02 00:58:50.456  |
+--------------------------+

Vitalii Diravka 7 年前

看起来你应该用 substr 然后转换为INT。

{ “时间戳”:1501580484655 }

0: jdbc:drill:zk=local> select to_timestamp(cast(substr(`timestamp`, 
1, 10) as INTEGER)) from dfs.`tmp/test.json`;
+------------------------+
|         EXPR$0         |
+------------------------+
| 2017-08-01 02:41:24.0  |
+------------------------+

earl 7 年前

这在从记录文件中选取时间戳列后效果良好。

 select to_timestamp(cast(CONCAT(substr(`Timestamp`, 1 , 10),'.',substr(`Timestamp`, CHAR_LENGTH(`Timestamp`)-3, 3)) as FLOAT)) from dfs.root.`/test/`;

+--------------------------+
|          EXPR$0          |
+--------------------------+
| 2017-08-04 10:00:01.792  |
+--------------------------+