CODEWITHSUNDEEP
hadoopimpala
Anna
Anna

Reputation: 464

Change a decimal string into a timestamp in Impala

How do you convert a string type like t1.updte_timestamp

2018-06-02-08.18.45.562742 
2018-05-26-09.18.16.594824

into a timestamp? SHOULD RESULT IN: 

2018-06-02-08.18.45
2018-05-26-09.18.16

ETC

The values had been imported from excel and are in STRING-TYPE

I tried:

SELECT
 to_timestamp(cast (t1.updte_timestamp as string), 'yyyy-mm-dd hh:mm:ss') as updted_timestamp FROM OLD;

but results in NULL for all values

thank you

Upvotes: 0

Views: 288

Answers (1)

hlagos
hlagos

Reputation: 7957

you can substr your string and apply to_timestamp as follow

select to_timestamp(substr('2018-06-02-08.18.45.562742', 1, 19) , 'yyyy-MM-dd-HH.mm.ss');

Make sure you use MM for month and HH for hour in upper case

Upvotes: 1

Related Questions