Hadoop - convertir string en timestamp dans la ruche - Stack Overflow

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?

Not sure what you mean by "better way" but you can always write your own function to handle the date conversion.

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.

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?

Not sure what you mean by "better way" but you can always write your own function to handle the date conversion.

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.

Source