Converting Epoch Time in String to date field in Impala SQL

Question

i have an epoch date time field which is stored as string in database table. i need to cast this to timestamp in impala sql. I am using the below format and not working. cast(unix_timestamp(execn_start_ts, "yyyy-MM-dd;HH:mm:ss") as timestamp) where execn_start_ts is the field from my table with unix/epoch time stored as string.

Answer 1

The approach below keeps the ms information if your epoch time ms provides it.

select CAST(CAST(ms_epoch_time as BIGINT) / 1000 AS TIMESTAMP)
as new_timestamp
from db.some_tabl_name

--ms_epoch_time - 1608249590791
--new_timestamp -  2020-12-17 23:59:50.790999889

Answer 2

Try use
unix_timestamp(string datetime, string format)

Impala datetime functions