Snowflake's time difference alternative to Postgres INTERVAL

Question

I have UNIX duration in ms, that needs to be converted to human readable format. In Postgres it is possible to do using INTERVAL type, but how can it be done in Snowflake? The challenge is that some of those values can exceed 24h. I've checked solution to a similar problem (with adjustments to ms) on Snowflake forum. But it doesnt support time > 24h.

Sample data:

7200000 -- 2h
28800000 --8h
1800000 -- 30min
252000000 -- 70h
86400000 -- 24h
3593314 -- 59min 53sec

So that the desired output would look like:

02:00:00
08:00:00
00:30:00
70:00:00
24:00:00
00:59:53

Maybe I am bad at searching, but so far I wasnt able to find a solution for this issue.

Solution that works in Postgres SELECT time_unix::bigint * INTERVAL '1 ms'

Answer 1

Wondering if someone can come up with a worse solution :)

create or replace function pginterval( TIME number )
returns varchar
language sql
as
$$
select  LPAD( (extract( day from to_timestamp( TIME, 3 )) - 1 ) * 24 + extract( hour from  to_timestamp( TIME, 3 )), 2, '0' )
|| ':' || right( to_timestamp( TIME, 3 )::time, 5 )
$$;

select pginterval( x )
from values 
(7200000),
(28800000),
(1800000),
(252000000),
(86400000),
(3593314) tmp (x);

PGINTERVAL( X )
02:00:00
08:00:00
00:30:00
70:00:00
24:00:00
00:59:53