HiveQL How to convert milliseconds in decimal format to mm:ss.SSS

Question

I'm trying to convert decimal timestamp in milliseconds to mm:ss.SSS . The method only works for integers, and the output is not what I desired:

select from_unixtime(cast(1911.13/1000 as bigint), 'mm:ss.SSS');

1911.13 milliseconds yields the output, 00:01.000, which is not correct, should be 00:01.911

I have tried to convert to double, but received errors

select from_unixtime(cast(1911.13/1000 as double), 'mm:ss.SSS');

error:[Code: 10014, SQL State: 42000] Error while compiling statement: FAILED: SemanticException [Error 10014]: Line 1:7 Wrong arguments ''mm:ss.SSS'': No matching method for class org.apache.hadoop.hive.ql.udf.UDFFromUnixTime with (double, string). Possible choices: FUNC(bigint) FUNC(bigint, string) FUNC(int) FUNC(int, string)

Any help will be appreciated!

Answer 1

Anything after decimal is milliseconds. So, you can split the string into two parts. Convert before decimal part using from_unixtime and concat it with after decimal part to get the whole data.

select cast(from_unixtime(1911, 'mm:ss') as string)|| rpad('.130',4,'0') as col_ms 

Pls note, millisecond .10 or .1 or .100 means 100 milliseconds, so i used rpad with 0 to pad it. You can create a generic sql as well.

select cast(from_unixtime( cast(substr((cast(x/y) as string) ,1,instr((cast(x/y) as string),'.')) as bigint,'mm:ss') || || rpad(substr((cast(x/y) as string),instr((cast(x/y) as string),'.')),4,'0') as col_ms