I'm getting API response for few fields in below format, Which I wanted to convert them into seconds when loading into snowflake target table. Is there any possibilities this can be converted using any of snowflake inbuilt function? else anyway to convert the source data into required format.
API response
"field1":"13 Days 1 Hour 51 Minutes",
"field2":"11 Hours 24 Minutes"
I want above response to be converted into seconds
field1 to --> '1129860'
field2 to --> '41040'
Thanks
A think a UDF would work best for this. It will be more readable. You can probably do this in a SQL UDF, but for this JavaScript works fine too.
create or replace function TIME_STRING_TO_SECONDS(TIME_STRING string)
returns float
language javascript
as
$$
var s = TIME_STRING.split(" ");
var seconds = 0;
for (var i = 1; i <= s.length; i = i + 2) {
seconds += getSeconds(s[i], s[i-1]);
}
return seconds;
function getSeconds(unit, sec) {
switch (unit.toUpperCase()) {
case 'DAYS':
return 86400 * sec;
case "DAY":
return 86400 * sec;
case 'HOURS':
return 3600 * sec;
case "HOUR":
return 3600 * sec;
case "MINUTES":
return 60 * sec;
case "MINUTE":
return 60 * sec;
default:
return sec;
}
}
$$;
You can then run the statements like so:
select TIME_STRING_TO_SECONDS('13 Days 1 Hour 51 Minutes');
select TIME_STRING_TO_SECONDS('11 Hours 24 Minutes');
Your format appears to indicate that the API is returning a JSON. Just parse the JSON using the standard Snowflake colon and dot notation, and pass each part into the UDF.
Remember that JavaScript does not have integer types, so if you need type as an integer you can do it explicitly in your SQL:
select TIME_STRING_TO_SECONDS('11 Hours 24 Minutes')::int;
Not a working code but an idea:
SELECT DATEDIFF(second,
'2000-01-01'::DATE,
'2000-01-01'::DATE + INTERVAL '11 Hours, 24 Minutes')
