Search code examples
sqlhql

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".

Solution

  • 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