How to convert a string to a millisecond timestamp in Hive

Question

I have a line "20141014123456789" that represents a timestamp with milliseconds that I need to convert to a timestamp in Hive (0.13.0) without losing a millisecond.

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

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

The job string works:

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

but my string is not in this form.

I think I need to reformat my line from '20141014123456789' to '2014-10-14 12: 34: 56.789'. My task is to do this without dirty concatenation of substrings.

Answer 1

I found a way to avoid 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) 

Answer 2

I do not think that this can be done without confusion. Because according to the documentation of the unix_timestamp () function, it returns the time in seconds and, therefore, omits part of the milliseconds.

"Convert the temporary string with the specified pattern to the Unix timestamp (in seconds), return 0 if fail: unix_timestamp ('2009-03-20', 'yyyy-MM-dd') = 1237532400."

The best option here is to write UDF to handle this, you want to avoid messy concatenations. However, concatenation (albeit erratic) would be better for the job.

Answer 3

I had a date field in this form 2015-07-22T09: 00: 32.956443Z (stored as a string). I needed to do some date manipulation. the following command, even though it worked fine for me :)

 select cast(concat(concat(substr(date_created,1,10),' '),substr(date_created,12,15)) as timestamp) from tablename; 

it looks confusing, but it's pretty easy if you break it. extracting the date and time with milliseconds and the concatenated space between them, and then merging it all and inserting it into the timestamp. now it can be used to process date or time.

Answer 4

Suppose you have a column 'Birth_date' in your table, which is in string format, you should use the following query to filter using Birth_date

 date_Format(birth_date, 'yyyy-MM-dd HH:mm:ssSSS') 

You can use it in the request as follows

 select * from yourtable where date_Format(birth_date, 'yyyy-MM-dd HH:mm:ssSSS') = '2019-04-16 07:12:59999'; 

Answer 5

A simple strategy would be to use date_format(arg1, arg2) , where arg1 is the timestamp as a formatted string, date or timestamp, and arg2 is the string format (in arg1 ). Refer to the SimpleDateFormat java documentation for a valid argument format.

So in this case:

 date_format('20141014123456789', 'yyyyMMddHHmmssSSS') 

the following line will be displayed: '2014-10-14 12:34:56.789' , which can then be discarded as a timestamp:

 cast(date_format('20141014123456789', 'yyyyMMddHHmmssSSS') as timestamp) 

The above statement returns a timestamp (optional).