Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Convert String to Timestamp with milliseconds in Hive

avatar
New Contributor

Hi,

I need to transform a string column into a timestamp with milliseconds .

I tried several formats :

 

 

 

select from_unixtime(unix_timestamp('2020.07.07 19:24:32.734000000','yyyy.MM.dd HH:mm:ssss'));
RESULT> 2020-07-07 19:24:32

 

 

 

 

 

 

 

select from_unixtime(unix_timestamp('2020.07.07 19:24:32.734000000','yyyy.MM.dd HH:mm:ss.SSS'));

RESULT> 2020-07-16 07:17:52

 

 

 

 But it didn't work.

 

 

1 REPLY 1

avatar
Super Guru

@elfz  I believe the functions you are working in only return unix timestamp to the seconds.     It is completely ignoring .SSS which should be the correct indication for 3 millisecond decimals.

 

 

Hive timestamps are able to be down to milliseconds with to 9 decimals.


Here is a great post on this topic:

 

https://community.cloudera.com/t5/Support-Questions/HIVE-datatype-Timestamp-with-miliseconds/td-p/15...