Sting to Timestamp (6) in Hive

[#1]

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

[#2]

Any inputs?

[#3 · Accepted solution]

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);