CODEWITHSUNDEEP
hiveunix-timestamp
sashi123
sashi123

Reputation: 15

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 ?

Upvotes: 0

Views: 5719

Answers (2)

philantrovert
philantrovert

Reputation: 10082

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  |
+------------------------+--+

Upvotes: 0

Sai Neelakantam
Sai Neelakantam

Reputation: 939

Your source DB has dot in between the hours, minutes and seconds. Hive supports : in between them like: yyyy-MM-dd HH:mm:ss.

Ref: Hive Date Functions

select
cast(
  concat(
    substr('2015-07-22-09.00.32',1,10), ' ', 
    substr('2015-07-22-09.00.32',12,2), ':', 
    substr('2015-07-22-09.00.32',15,2), ':', 
    substr('2015-07-22-09.00.32',18,2)
  ) AS TIMESTAMP
)
;

Upvotes: 1

Related Questions