Hive SQL cast string as timestamp without losing the milliseconds

Question

I have string data in the form 2020-10-21 12:49:27.090

I want to cast it as a timestamp. When I do this: select cast(column_name as timestamp) as column_name from table_name

all of the milliseconds are dropped, like this: 2020-10-21 12:49:27

I also tried this: select cast(date_format(column_name,'yyyy-MM-dd HH:mm:ss.SSS') as timestamp) as column_name from table_name

and the same problem persists, it drops the milliseconds.

How do I get it to convert strings to timestamps without losing the milliseconds?

Answer 1

Tested in Hive 2.3.6 on Qubole and on demo.gethue.com, works good:

select cast ('2020-10-21 12:49:27.090' as timestamp), 
       timestamp('2020-10-21 12:49:27.090')

Result:

2020-10-21 12:49:27.09    2020-10-21 12:49:27.09

And this

cast(date_format('2020-10-21 12:49:27.090','yyyy-MM-dd HH:mm:ss.SSS') as timestamp)

also works the same.

It seems like some problem with your client applpication or Hive version