question Sting to Timestamp (6) in Hive in Support Questions

Sting to Timestamp (6) in Hive

APentyala — Sun, 26 Jan 2025 14:41:08 GMT

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

Re: Sting to Timestamp (6) in Hive

APentyala — Tue, 04 Feb 2025 07:44:41 GMT

Any inputs?

Re: Sting to Timestamp (6) in Hive

ggangadharan — Tue, 04 Feb 2025 14:33:33 GMT

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