Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Convert String to Timestamp with milliseconds in Hive

Convert String to Timestamp with milliseconds in Hive

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
Highlighted

Re: Convert String to Timestamp with milliseconds in Hive

@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...

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

Don't have an account?
Coming from Hortonworks? Activate your account here