Convert string into timestamp in Hive

datetime hadoop hive impala timestamp-with-timezone
Petros Tsialiamanis picture Petros Tsialiamanis · Oct 11, 2017 · Viewed 10.8k times · Source

I have a value '2017-09-27T19:25:15.927-07:00', is there any way to convert this into a timestamp? I use Hive 1.1.0.

select unix_timestamp("2017-09-27T19:25:15.927-07:00", "yyyy-MM-ddTHH:mm:ss.SSSX") but it trows Bad date/time conversion format

select unix_timestamp("2017-09-27T19:25:15.927-07:00", "yyyy-MM-ddTHH:mm:ss.SSSZZZ") but it returns NULL

Answer

VK_217 picture VK_217 · Oct 11, 2017

The format is yyyy-MM-dd'T'HH:mm:ss.SSSXXX".Note the single quotes surrounding 'T'

select from_unixtime(unix_timestamp("2017-09-27T19:25:15.927-07:00", "yyyy-MM-dd'T'HH:mm:ss.SSSXXX"))

Related questions

Converting Unix epoch time to extended ISO8601

I have 3 tables I would like to work on using the date, however one of the tables includes the date in unix epoch format. Here is an example of the 3 fields: Table1: 2017-02-01T07:58:40.756031Z Table2: 2017-02-07T10:16:46…

Read More
How to get the current time in Python

What is the module/method used to get the current time?

Read More
Converting string into datetime

I've got a huge list of date-times like this as strings: Jun 1 2005 1:33PM Aug 28 1999 12:00AM I'm going to be shoving these back into proper datetime fields in a database so I need to magic them into real datetime objects. This …

Read More