Convert UTC timestamp to yyyyMMddHHmmss.SSS format in hive

Question

I have a scenario like below in hive

convert the current_timestamp to UTC. I am able to do so

select to_utc_timestamp(current_timestamp, 'America/Los_Angeles)';

Result:

2020-02-04 10:00:06.162

Next convert this resulting timestamp to yyyyMMddHHmmssSSS format.

I have tried like below

select from_unixtime((to_utc_timestamp(current_timestamp, 'America/Los_Angeles)', 'yyyy-MM-dd HH:mm:ss.SSS'), 'yyyyMMddHHmmssSSS');

I am unable to get the desired result.

expected result is 20200204100006162

Answer 1

You can use the date_format function if the Hive version >= 1.2.0.

select date_format(to_utc_timestamp(current_timestamp, 'America/Los_Angeles'),'yyyyMMddHHmmssSSS')