Search code examples
hiveunix-timestamp

How to Convert date field in yyyy-MM-dd-hh.mm.ss” to Timestamp field(yyyy-MM-dd hh:mm:ss) in Hive)

I have a date field in the format of yyyy-MM-dd-hh.mm.ss coming from a db2 source.I want to load into hive and convert to timestamp.

How do I achieve it ?

Solution

  • You can use a combination of unix_timestamp and from_unixtime instead of the substr method that you are currently using.

    select cast( 
           from_unixtime(
                unix_timestamp('2017-08-31-12:24:48' , 'yyyy-MM-dd-HH:mm:ss') 
           )
        as timestamp
);

+------------------------+--+
|          _c0           |
+------------------------+--+
| 2017-08-31 12:24:48.0  |
+------------------------+--+