CODEWITHSUNDEEP
hadooptimestamphive
David Ford
David Ford

Reputation: 351

How to convert a string to timestamp with milliseconds in Hive

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.

Upvotes: 13

Views: 101109

Answers (5)

Terminator17
Terminator17

Reputation: 860

Let say 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 a query in the following way

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

Upvotes: 1

Vini
Vini

Reputation: 21

i had the date field in this form 2015-07-22T09:00:32.956443Z(stored as string). i needed to do some date manipulations. the following command even though little messy worked fine for me:)

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

this looks confusing but it is quite easy if you break it down. extracting the date and time with milliseconds and concat a space in between and then concat the whole thing and casting it into timestamp. now this can be used for date or timestamp manipulations.

Upvotes: 1

FMeh
FMeh

Reputation: 31

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

So, in this case:

date_format('20141014123456789', 'yyyyMMddHHmmssSSS')

would yield the following string: '2014-10-14 12:34:56.789' which can then be cast as timestamp:

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

The above statement would return timestamp (as desired).

Upvotes: 2

David Ford
David Ford

Reputation: 351

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)

Upvotes: 12

Amar
Amar

Reputation: 3845

I don't think this can be done without being messy. Because according to the unix_timestamp() function documentation it returns the time is seconds and hence will omit the milliseconds part.

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

Best option here would be to write a UDF to handle this is you want to avoid messy concatenations. However the concatenation (though messy) would be better to the job.

Upvotes: 0

Related Questions