Convert an ISO8601 datetime format string to date and time stamp in Impala

I have a string of the form: yyyy-MM-dd'T'HH:mm:ss.SSSXXX (ie : 2016-12-11T14:21:04.354-08:00) and I would like to convert it to timestamp. I have tried : unix_timestamp( timestamp , "yyyy-MM-dd'T'HH:mm:ss.SSSXXX") but it throws : 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

For Impala, your time format string should look like this: 'yyyy-mm-ddthh:mm:ss.sss-hh:mm'

See this example:

[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] >
我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章