如何将PnDTnHnMn.nS格式时长解析为Snowflake区间或Unix Epoch值

Snowflake没有提供直接解析该类时长字符串的内置函数，最简洁且鲁棒的方案是通过正则提取各时间分量，直接转换为总秒级偏移量，或组装为Snowflake原生支持的Interval类型，即可直接完成与Time/DateTime类型的加减运算。

可直接复用的正则解析SQL #

该方案覆盖所有标准格式场景：支持正负分量、小数秒、任意分量缺省的合法输入，比逐字符截取的逻辑容错性高很多，不会因为某段时间单位不存在出现计算偏差。
核心正则会按顺序捕获天、小时、分钟、秒四个时间分量，缺省分量自动按0处理：

with sample_data as (
    select * from values
        ('PT7H30M'),
        ('PT-12H15M'),
        ('P3DT2H10M5.5S'),
        ('PT45S'),
        ('P1D')
    as val(iso_duration)
)
select
    iso_duration,
    -- 提取各时间分量，缺省值补0
    zeroifnull(regexp_substr(iso_duration, '^P(?:(-?\\d+(?:\\.\\d+)?)D)?(?:T(?:(-?\\d+(?:\\.\\d+)?)H)?(?:(-?\\d+(?:\\.\\d+)?)M)?(?:(-?\\d+(?:\\.\\d+)?)S)?)?$', 1,1,'e',1))::float as days,
    zeroifnull(regexp_substr(iso_duration, '^P(?:(-?\\d+(?:\\.\\d+)?)D)?(?:T(?:(-?\\d+(?:\\.\\d+)?)H)?(?:(-?\\d+(?:\\.\\d+)?)M)?(?:(-?\\d+(?:\\.\\d+)?)S)?)?$', 1,1,'e',2))::float as hours,
    zeroifnull(regexp_substr(iso_duration, '^P(?:(-?\\d+(?:\\.\\d+)?)D)?(?:T(?:(-?\\d+(?:\\.\\d+)?)H)?(?:(-?\\d+(?:\\.\\d+)?)M)?(?:(-?\\d+(?:\\.\\d+)?)S)?)?$', 1,1,'e',3))::float as minutes,
    zeroifnull(regexp_substr(iso_duration, '^P(?:(-?\\d+(?:\\.\\d+)?)D)?(?:T(?:(-?\\d+(?:\\.\\d+)?)H)?(?:(-?\\d+(?:\\.\\d+)?)M)?(?:(-?\\d+(?:\\.\\d+)?)S)?)?$', 1,1,'e',4))::float as seconds,
    -- 转换为Unix秒级偏移量
    (days * 86400 + hours * 3600 + minutes * 60 + seconds) as total_epoch_seconds,
    -- 转换为Snowflake原生Interval类型，可直接与时间类型运算
    (days || ' days ' || hours || ' hours ' || minutes || ' minutes ' || seconds || ' seconds')::interval as snowflake_interval
from sample_data;

运算使用方式 #

解析完成后可直接和时间字段做加减计算，两种写法都支持：

• 用Interval字段运算：select current_timestamp() + snowflake_interval from xxx
• 用秒级偏移运算：select time_col + total_epoch_seconds * interval '1 second' from xxx

扩展说明 #

• 该正则完全兼容标准PnDTnHnMn.nS格式的所有合法输入，包括示例中提到的PT-12H15M这类带负分量的格式。
• 如果业务场景需要处理带年、月维度的ISO-8601时长（格式为PnYnMnDTnHnMn.nS），只需要在正则最前端补充两个捕获组匹配年、月分量，直接拼接进Interval字符串即可；注意年、月属于变长时间单位，转秒级偏移时需要根据业务规则取平均换算值。
• 高频使用场景可以把这段逻辑封装为自定义UDF，入参为ISO时长字符串，返回Interval类型或总秒数，简化日常调用。

内容的提问来源于stack exchange，提问作者archjkeee