Hive: Convert string datetime with missing seconds in "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"

I'm using the following code to convert a string datetime variable to datetime, but the converted string is missing SSS part.

Code used:

cast(FROM_UNIXTIME(UNIX_TIMESTAMP(oldtime, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd HH:mm:ss.SSS") as timestamp) as newtime

The outcome:

2019-03-08T18:28:36.901Z is converted to 08MAR2019:18:28:36.000000

Some other oldtimes in string:

2020-03-09T16:05:06:827Z
2020-03-09T16:03:19:354Z
2020-03-11T16:03:57:280Z
2020-03-10T16:02:57:642Z
2020-03-10T16:04:07:455Z
2020-03-10T16:04:09:737Z
2020-03-10T16:03:57:280Z
2020-03-10T16:02:46:816Z

The SSS part '901' is missing in the converted time. Would like help on keeping the SSS part since I need to sort the records by their exact time.

Thank you!

Solution

from_unixtime is always until minutes(yyyy-MM-dd HH:mm:ss) to get millisecs we need to do some workarounds.

we will extract the millisecs from the old_time using regexp_extract then concat that to from_unixtime result and finally cast to timestamp.

Example:

select old_time,
timestamp(concat_ws(".", --concat_ws with . and cast
FROM_UNIXTIME(UNIX_TIMESTAMP(old_time, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd HH:mm:ss"), -- from_unixtime and unix_timestamp to convert without millisecs
regexp_extract(string(old_time),".+\\.(.*)(?i)z",1))) as newtime from --regexp_extract to extract last 3 digits before z then concat
(select string("2020-03-11T21:14:41.335Z")old_time)e

+------------------------+-----------------------+
|old_time                |newtime                |
+------------------------+-----------------------+
|2020-03-11T21:14:41.335Z|2020-03-11 21:14:41.335|
+------------------------+-----------------------+

UPDATE:

Your sample data have : before milliseconds, Try with below query:

select old_time,
    timestamp(concat_ws(".", --concat_ws with . and cast
    FROM_UNIXTIME(UNIX_TIMESTAMP(old_time, "yyyy-MM-dd'T'HH:mm:ss:SSS'Z'"),"yyyy-MM-dd HH:mm:ss"), -- from_unixtime and unix_timestamp to convert without millisecs
    regexp_extract(string(old_time),".+\\:(.*)(?i)z",1))) as newtime from --regexp_extract to extract last 3 digits before z then concat
    (select string("2020-03-11T21:14:41:335Z")old_time)e