CODEWITHSUNDEEP
jsondatetimehadoophiveunix-timestamp
user3366123
user3366123

Reputation: 1

Errors in converting timstamp to hive format in hive

I have a JSON data in my hive table which contains a time in following format in milliseconds:

...."internal":{"time":["1393404205891"]....

I want to get the time in YYYY-MM-DD HH:MM:SS format so that I can query it later to get records only for particular hour duration. I have tried following ways but still not able to get the time in desired format.

1st TRY:

select from_unixtime(cast(get_json_object(log_json,'$.internal.time[0]') as bigint)/1000, 'YYYY-MM-DD HH:MM:SS') as time_unix
from slog_table

ERROR:

No matching method for class org.apache.hadoop.hive.ql.udf.UDFFromUnixTime with (double, string)

2nd TRY:

select from_unixtime (cast ('1393526016039' as int)/1000, 'YYYY-MM-DD HH:MM:SS') as time_unix
from slog_table

Upvotes: 0

Views: 938

Answers (1)

A Praveen Kumar
A Praveen Kumar

Reputation: 303

Below query should work:

select from_unixtime (cast (1393526016039/1000 as bigint), 'YYYY-MM-dd HH:mm:SS') as time_unix from slog_table

Upvotes: 1

Related Questions