Handle timezone inferior to unix epoch time

user_18891329 0
Viewed 36

I want to md5 a timestamp column in Hive, without the millisecond. If timestamp is before Epoch Unix Time (year 1970), timestamp is corrupted: START_DATE=1915-07-15 23:25:26.290448384

select ID, START_DATE, MD5(START_DATE) from TABLE1

Result : START_DATE = 2500-02-02 00:00:00.0

No issue without adding MD5 function, or if the timestamp > 1970.

I've tried with vectorized parameter (https://cwiki.apache.org/confluence/display/hive/vectorized+query+execution#VectorizedQueryExecution-Limitations) but still the same issue. Also tried : Cast as string, substr... before MD5.

How can we handle timestamp < 1970 ?

hive timestamp
1 Answers

can you use somethign like this ?

select md5(from_unixtime(unix_timestamp(substring('1915-07-15 23:25:26.290448384',1,19)))) as md5_out

output

Unixtime will calculate any date before 1970 as negative number and calculate accordingly. so i think it should be alright.

user_64305230
Related