question Re: convert ISO8601 date in d mm yyhh mm ss format in Hive in Support Questions

convert ISO8601 date in d mm yyhh mm ss format in Hive

simran_k — Mon, 10 Oct 2016 17:07:03 GMT

My date looks like this:

2016-09-13T06:03:51Z

I need to convert it in dd-mm-YYYY hh:mm:ss format. HOw do I do it?

I tried:

from_unixtime(unix_timestamp(created_at , 'dd-MM-yyyy HH-mm-ss'))

but it didnot work and gives null

Re: convert ISO8601 date in d mm yyhh mm ss format in Hive

bwilson — Tue, 11 Oct 2016 03:05:56 GMT

Hi @Simran Kaur,

You can still achieve this using out-of-the-box functions in Hive as you mentioned. You just missed getting the string in the right format. For clarity, the basic steps are:

Replace the 'T' in the string with a space so the date is in the format expected by Hive.
Convert the string to a unix timestamp using the unix_timestamp function.
Convert the timestamp to your preferred date format using the from_unixtime function.

Here is a quick example you can run in Hive to see the result for the string you provided:

select from_unixtime(unix_timestamp(regexp_replace('2016-09-13T06:03:51Z', 'T',' ')), 'dd-MM-yyyy HH-mm-ss');

Notice that the only additional step is the replace operation.

Re: convert ISO8601 date in d mm yyhh mm ss format in Hive

theepireddysant — Thu, 23 Nov 2017 13:46:42 GMT

if I am using above function getting null . Please reply