Parse PnDTnHnMn.nS (ISO-8601) duration into the Snowflake Interval or Epoch value

user_12948786 0
Viewed 105

How to parse/convert duration in the PnDTnHnMn.nS format to the Snowflake interval? Or to at least to Unix Epoch numeric value?

I need that to add such values to the Time/DateTime values.

I'm thinking about something straightforward to at least parse "easy" values like "PT7H30M":

with sample_data as (select 'PT-12H15M' as val)
    select
        val,
        case
        when position('H' in val) != 0 then
            substr(val, position('T' in val) + 1, position('H' in val) - position('T' in val) - 1)
        end as hours
    from sample_data

But I'm thinking maybe there is some more elegant way to do that (some regexp for example).

snowflake-cloud-data-platform iso8601 sql
1 Answers

You can create a Java UDF to parse it with perfect compatibility (ISO 8601):

create or replace function duration_to_seconds( DUR varchar )
returns integer
language java
handler='MyConverter.duration_to_seconds'
target_path='@~/MyConverter.jar'
as
$$
    import java.time.Duration;

    class MyConverter {
    
        public static int duration_to_seconds( String DUR ) {

            Duration duration = Duration.parse( DUR );
            
            return (int)duration.getSeconds();

        }
    }
$$;

select duration_to_seconds( 'PT-12H15M' );
user_125509650
Related