CODEWITHSUNDEEP
hadoophivebigdata
Siva
Siva

Reputation: 35

How to convert string date to big int in hive with milliseconds

I have a string 2013-01-01 12:00:01.546 which represents a timestamp with milliseconds that I need to convert to a bigint without losing the milliseconds.

I tried unix_timestamp but I lose the milliseconds:

unix_timestamp(2013-01-01 12:00:01.546,'yyyy-MM-dd HH:mm:ss') ==> 1357059601
unix_timestamp(2013-01-01 12:00:01.786,'yyyy-MM-dd HH:mm:ss') ==> 1357059601

I tried with milliseconds format as well but no difference

unix_timestamp(2013-01-01 12:00:01.786,'yyyy-MM-dd HH:mm:ss:SSS') ==> 1357059601

Is there any way to get milliseconds difference in hive?

Upvotes: 1

Views: 3001

Answers (3)

Rakesh Chintha
Rakesh Chintha

Reputation: 705

Hive function unix_timestamp() doesn't convert the milli second part, so you may want to use the below:

unix_timestamp('2013-01-01 12:00:01.546') + cast(split('2013-01-01 12:00:01.546','\\\.')[1] as int) => 1357067347
unix_timestamp('2013-01-01 12:00:01.786') + cast(split('2013-01-01 12:00:01.786','\\\.')[1] as int) => 1357067587

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

This is what I came with so far.
If all your timestamps have a fraction of 3 digits it can be simplified.

with t as (select timestamp '2013-01-01 12:00:01.546' as ts)
select  cast ((to_unix_timestamp(ts) + coalesce(cast(regexp_extract(ts,'\\.\\d*',0) as decimal(3,3)),0)) * 1000 as bigint)
from    t

1357070401546

Verification of the result:

select from_utc_timestamp (1357070401546,'UTC')

2013-01-01 12:00:01.546000

Upvotes: 2

philantrovert
philantrovert

Reputation: 10082

So apparently unix_timestamp doesn't convert milliseconds. You can use the following approach.

hive> select unix_timestamp(cast(regexp_replace('2013-01-01 12:00:01.546', '(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2}).(\\d{3})', '$1-$2-$3 $4:$5:$6.$7' ) as timestamp));
OK
1357063201

Upvotes: 1

Related Questions