Support Questions

Find answers, ask questions, and share your expertise

Sting to Timestamp (6) in Hive

avatar
Explorer

Hi Team,

I’m seeking your support in converting a string to a timestamp with microseconds in Hive. When using unixtimestamp, I notice that the milliseconds are being dropped. Could you please advise on how to capture milliseconds (and microseconds) correctly, and which data type I should use to store the value in Hive?

SELECT from_unixtime(unix_timestamp('2024-11-05-10.03.17.872195', 'yyyy-MM-dd-HH.mm.ss.SSSSSS')) as times,'2024-11-05-10.03.17.872195' as vale
FROM

APentyala_0-1737902409390.png

 

 

apentyala
2 REPLIES 2

avatar
Explorer

Any inputs?

apentyala

avatar
Master Collaborator

Use CAST to convert to TIMESTAMP type. 

SELECT CAST('2024-11-05 10:03:17.872195' AS TIMESTAMP) AS timestamp_value;

We can also try TIMESTAMP WITH LOCAL TIME ZONE, This helps retain precision when dealing with timezones.

SELECT CAST('2024-11-05 10:03:17.872195' AS TIMESTAMP WITH LOCAL TIME ZONE);