Support Questions
Find answers, ask questions, and share your expertise

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

Solved Go to solution

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

Expert Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

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.

View solution in original post

2 REPLIES 2

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

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.

View solution in original post

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

if I am using above function getting null . Please reply