将iso8601 datetime格式的字符串转换为impala中的时间戳

von4xj4u 于 2021-06-28 发布在 Hive

关注(0)|答案(2)|浏览(418)

我有一个字符串的形式： yyyy-MM-dd'T'HH:mm:ss.SSSXXX （即：2016-12-11t14:21:04.354-08:00）我想将其转换为时间戳。我试过： unix_timestamp( 时间戳 , "yyyy-MM-dd'T'HH:mm:ss.SSSXXX") 但它抛出： WARNINGS: Bad date/time conversion format: yyyy-MM-ddHH:mm:ss.SSSXXX ```
Query: select unix_timestamp('2016-12-11T14:21:04.354-08:00', "yyyy-MM-dd'T'HH:mm:ss.SSSXXX")
WARNINGS: Bad date/time conversion format: yyyy-MM-ddHH:mm:ss.SSSXXX

Bad date/time conversion format: yyyy-MM-ddHH:mm:ss.SSSXXX

Hive impala DateTime Date ISO8601

来源：https://stackoverflow.com/questions/41168842/convert-a-string-of-iso8601-datetime-format-to-timestamp-in-impala

2条答案

按热度按时间

ffdz8vbo1#

select from_unixtime( unix_timestamp( 
  concat(substr('2016-12-11T14:21:04.354-05:00', 1, 26), '00'), 'yyyy-MM-dd\'T\'HH:mm:ss.SSSZ'), 'yyyy-MM-dd HH:mm:ss Z');

0: jdbc:hive2://localhost:10000/default> select from_unixtime( unix_timestamp( concat(substr('2016-12-11T14:21:04.354-05:00', 1, 26), '00'), 'yyyy-MM-dd\'T\'HH:mm:ss.SSSZ'), 'yyyy-MM-dd HH:mm:ss Z');
INFO  : Compiling command(queryId=hive_20170307142929_85757745-69f9-46b8-bbaf-9a2b9d0d8904): select from_unixtime( unix_timestamp( concat(substr('2016-12-11T14:21:04.354-05:00', 1, 26), '00'), 'yyyy-MM-dd\'T\'HH:mm:ss.SSSZ'), 'yyyy-MM-dd HH:mm:ss Z')
INFO  : OK
+----------------------------+--+
|            _c0             |
+----------------------------+--+
| 2016-12-12 03:21:04 +0800  |
+----------------------------+--+
1 row selected (1.416 seconds)

赞(0）回复(0）举报 2021-06-28

s8vozzvw2#

对于impala，时间格式字符串应如下所示： 'yyyy-mm-ddthh:mm:ss.sss-hh:mm' 请参见此示例：

[localhost:21000] > select unix_timestamp('2016-12-11T14:21:04.354-05:00', "yyyy-MM-ddTHH:mm:ss.SSS-hh:mm");
Query: select unix_timestamp('2016-12-11T14:21:04.354-05:00', "yyyy-MM-ddTHH:mm:ss.SSS-hh:mm")
Query submitted at: 2016-12-21 00:49:48 (Coordinator: http://lv-desktop:25000)
Query progress can be monitored at: http://lv-desktop:25000/query_plan?query_id=564166231bc9fd4d:36e038bd00000000
+----------------------------------------------------------------------------------+
| unix_timestamp('2016-12-11t14:21:04.354-05:00', 'yyyy-mm-ddthh:mm:ss.sss-hh:mm') |
+----------------------------------------------------------------------------------+
| 1481484064                                                                       |
+----------------------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s
[localhost:21000] > select unix_timestamp('2016-12-11T14:21:04.354-08:00', "yyyy-MM-ddTHH:mm:ss.SSS-hh:mm");
Query: select unix_timestamp('2016-12-11T14:21:04.354-08:00', "yyyy-MM-ddTHH:mm:ss.SSS-hh:mm")
Query submitted at: 2016-12-21 00:49:56 (Coordinator: http://lv-desktop:25000)
Query progress can be monitored at: http://lv-desktop:25000/query_plan?query_id=484ba21cc5d78ff8:e8d8353a00000000
+----------------------------------------------------------------------------------+
| unix_timestamp('2016-12-11t14:21:04.354-08:00', 'yyyy-mm-ddthh:mm:ss.sss-hh:mm') |
+----------------------------------------------------------------------------------+
| 1481494864                                                                       |
+----------------------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s
[localhost:21000] >

赞(0）回复(0）举报 2021-06-28

我来回答

将iso8601 datetime格式的字符串转换为impala中的时间戳

2条答案

相关问题

热门标签

最新问答