使用regex可以解析case语句中所有可能的模板。也许这可以优化，我希望你有这个想法。添加更多模板并进行如下测试：

with mytable as(
select stack(6,
'1 Day 8 Hours 48 Minutes',    
'1 Hour 1 Minutes',   
'3 Hours', 
'20 Minutes',
'20 Minutes 4 Seconds',
'50 Seconds' 
) as mytimestamp 
)

select mytimestamp, ts[0]*86400  --days
                   +ts[1]*3600   --hours
                   +ts[2]*60     --minutes
                   +ts[3]        --seconds 
                   as seconds
from 
(
select mytimestamp, 
       split(
        case when mytimestamp rlike '^(\\d{1,2})\\s(?:Days?)\\s(\\d{1,2})\\s(?:Hours?)\\s(\\d{1,2})\\s(?:Minutes?)$'                            --Days Hours Minutes
                 then regexp_replace(mytimestamp,'^(\\d{1,2})\\s(?:Days?)\\s(\\d{1,2})\\s(?:Hours?)\\s(\\d{1,2})\\s(?:Minutes?)$','$1:$2:$3:0')

            when mytimestamp rlike '^(\\d{1,2})\\s(?:Hours?)\\s(\\d{1,2})\\s(?:Minutes?)$'                                                     --Hours Minutes
                 then regexp_replace(mytimestamp,'^(\\d{1,2})\\s(?:Hours?)\\s(\\d{1,2})\\s(?:Minutes?)$','0:$1:$2:0')

            when mytimestamp rlike '^(\\d{1,2})\\s(?:Hours?)$'                                                                                 --Hours
                 then regexp_replace(mytimestamp,'^(\\d{1,2})\\s(?:Hours?)$','0:$1:0:0')

            when mytimestamp rlike '^(\\d{1,2})\\s(?:Minutes?)$'                                                                               --Minutes
                 then regexp_replace(mytimestamp,'^(\\d{1,2})\\s(?:Minutes?)$','0:0:$1:0')

            when mytimestamp rlike '^(\\d{1,2})\\s(?:Minutes?)\\s(\\d{1,2})\\s(?:Seconds?)$'                                                   --Minutes Seconds
                 then regexp_replace(mytimestamp,'^(\\d{1,2})\\s(?:Minutes?)\\s(\\d{1,2})\\s(?:Seconds?)$','0:0:$1:$2') 

            when mytimestamp rlike '^(\\d{1,2})\\s(?:Seconds?)$'                                                                               --Seconds
                 then regexp_replace(mytimestamp,'^(\\d{1,2})\\s(?:Seconds?)$','0:0:0:$1')
         end,':') as ts
    from mytable
)s

退货：

mytimestamp                seconds  
1 Day 8 Hours 48 Minutes    118080  
1 Hour 1 Minutes            3660    
3 Hours                     10800   
20 Minutes                  1200    
20 Minutes 4 Seconds        1204    
50 Seconds                  50