How to convert a string to timestamp with milliseconds in Hive

Question

I have a string '20141014123456789' which represents a timestamp with milliseconds that I need to convert to a timestamp in Hive (0.13.0) without losing the milliseconds.

I tried this but unix_timestamp returns an integer, so I lose the milliseconds:

from_unixtime(unix_timestamp('20141014123456789', 'yyyyMMddHHmmssSSS'))      >> 2014-10-14 12:34:56    

Casting a string works:

cast('2014-10-14 12:34:56.789' as timestamp)      >> 2014-10-14 12:34:56.789

but my string isn't in that form.

I think I need to reformat my string from '20141014123456789' to '2014-10-14 12:34:56.789'. My challenge is how to do that without a messy concatenation of substrings.

Answer 1

I found a way to avoid the messy concatenation of substrings using the following code:

select cast(regexp_replace('20141014123456789', 
                           '(\\d{4})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{3})',
                           '$1-$2-$3 $4:$5:$6.$7') as timestamp)