'Handle timezone inferior to unix epoch time
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 ?
Solution 1:[1]
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
Unixtime will calculate any date before 1970 as negative number and calculate accordingly. so i think it should be alright.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Koushik Roy |