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

[#1]

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

[#2 · Accepted solution]

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:

1. Replace the 'T' in the string with a space so the date is in the format expected by Hive.
2. Convert the string to a unix timestamp using the unix_timestamp function.
3. 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.

[#3]

if I am using above function getting null . Please reply