Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
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

avatar

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

avatar

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.

avatar

if I am using above function getting null . Please reply