CODEWITHSUNDEEP
snowflake-cloud-data-platform
Aleeb
Aleeb

Reputation: 45

Snowflake's time difference alternative to Postgres INTERVAL

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'

Upvotes: 0

Views: 304

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

select *
    ,floor(ms/3600000) as hh
    ,floor((ms%3600000)/60000) as mm
    ,floor((ms%60000)/1000) as ss
    ,nullifzero(hh) as a1
    ,nullifzero(mm) as a2
    ,nullifzero(ss) as a3
    ,trim(nvl(a1||'h ','') || nvl(a2||'m ','') || nvl(a3||'s','')) as r 
from values
    (7200000, '2h'),
    (28800000 , '8h'),
    (1800000, '30min'),
    (252000000, '70h'),
    (86400000, '24h'),
    (3593314, '59min 53sec')
    t(ms, wanted)

gives:

MS WANTED HH MM SS A1 A2 A3 R
7,200,000 2h 2 0 0 2 2h
28,800,000 8h 8 0 0 8 8h
1,800,000 30min 0 30 0 30 30m
252,000,000 70h 70 0 0 70 70h
86,400,000 24h 24 0 0 24 24h
3,593,314 59min 53sec 0 59 53 59 53 59m 53s

thus:

select *
    ,trim(nvl(nullifzero(floor(ms/3600000))||'h ','') || nvl(nullifzero(floor((ms%3600000)/60000))||'m ','') || nvl(nullifzero(floor((ms%60000)/1000))||'s','')) as r 
from values
    (7200000, '2h'),
    (28800000 , '8h'),
    (1800000, '30min'),
    (252000000, '70h'),
    (86400000, '24h'),
    (3593314, '59min 53sec')
    t(ms, wanted)

gives:

MS WANTED R
7,200,000 2h 2h
28,800,000 8h 8h
1,800,000 30min 30m
252,000,000 70h 70h
86,400,000 24h 24h
3,593,314 59min 53sec 59m 53s

OR I could read:

select *
    ,trim(nvl(nullifzero(floor(ms/3600000))||'h ','') || nvl(nullifzero(floor((ms%3600000)/60000))||'m ','') || nvl(nullifzero(floor((ms%60000)/1000))||'s','')) as r 
    ,lpad(floor(ms/3600000), 2, 0) || ':' || lpad(floor((ms%3600000)/60000), 2, 0) || ':' || lpad(floor((ms%60000)/1000), 2, 0) as r2
from values
    (7200000, '2h'),
    (28800000 , '8h'),
    (1800000, '30min'),
    (252000000, '70h'),
    (86400000, '24h'),
    (3593314, '59min 53sec')
    t(ms, wanted)
MS WANTED R R2
7,200,000 2h 2h 02:00:00
28,800,000 8h 8h 08:00:00
1,800,000 30min 30m 00:30:00
252,000,000 70h 70h 70:00:00
86,400,000 24h 24h 24:00:00
3,593,314 59min 53sec 59m 53s 00:59:53

Upvotes: 0

Gokhan Atil
Gokhan Atil

Reputation: 10039

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

Upvotes: 1

Related Questions