Convert string to timestamp in Hive

Question

I have the following string representation of a timestamp in my Hive table:

20130502081559999

I need to convert it to a string like so:

2013-05-02 08:15:59

I have tried following ({code} >>> {result}):

from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmss')) >>> 2013-05-03 00:54:59
from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssMS')) >>> 2013-09-02 08:15:59
from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssMS')) >>> 2013-05-02 08:10:39

Converting to a timestamp and then unixtime seems weird, what is the proper way to do this?

EDIT I figured it out.

from_unixtime(unix_timestamp(substr('20130502081559999',1,14), 'yyyyMMddHHmmss')) >>> 2013-05-02 08:15:59

or

from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssSSS')) >>> 2013-05-02 08:15:59

Still... Is there a better way?

Answer 1

Looks like your format has three millisecond digits. I'd guess that, according to the SimpleDateFormat, you would need to use the following:

from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssSSS'))

Hope that helps.