Hive understands this format: 'yyyy-MM-dd HH:mm:ss.SSS'
.
Use unix_timestamp()
to convert to seconds passed from 1970-01-01, then use from_unixtime()
to convert to proper format:
select from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z'"),"yyyy-MM-dd HH:mm:ss");
Result:
2017-01-01 05:01:10
Update. This method is to remove Z
and replace T
with space using regexp_replace
and convert to timestamp if necessary, without using unix_timestamp()
, this will preserve milliseconds:
select timestamp(regexp_replace("2019-05-17T17:03:09.775Z", '^(.+?)T(.+?)Z$','$1 $2'));
Result:
2019-05-17 17:03:09.775