Date format conversion to YYYY-MM-DD HH:MIN:SEC in Apache Hive

I am loading data to a hive table from a csv file. File is having a field named last_updated_date and it’s value is in format “20200412013000771+0000”. I need to covert it into “YYYY-MM-DD HH:MIN:SEC”.

Answer

You can either chop it up with lots of substrings and rebuild it with concats, or strip off the irrelevant characters at the end and use unix_timestamp and from_unixtime.

Since you don’t care about milliseconds, you only want the first 14 chars. Here’s how I’d go about it:

select from_unixtime (unix_timestamp(substring('20200412013000771+0000',1,14), 'yyyyMMddhhmmss'))

Which returns

2020-04-12 01:30:00