Search code examples
datetimehadoophiveimpalatimestamp-with-timezone

Convert string into timestamp in Hive

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

Solution

  • 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"))