Bigquery iso8601 字符串到时间戳

Question

在我的数据集中，我有一个变量< code>duration，其中有200万行< code>ISO 8601格式的数据。示例格式:< code>PT21S或< code>PT5M29S，< code>PT1M16S

我在将此字符串变量转换为时间时遇到问题。目前我正在使用查询：

REGEXP_EXTRACT(duration, r'[^\d]+') as time,

通过该查询，可以区分持续时间是小时、分钟还是秒。我想将字符串持续时间转换为时间。我用过Regex和cast，但没有成功。当我尝试将字符串转换为时间时，我得到了错误：

“无效时间戳：'15S'”

"无效的时间戳：'1M'

“错误的 int64 值： PT11M16S”

我遇到的最大问题是字符串永远不会相同。

更多字符串示例:

PT48S
PT43M40S 
PT7M54S 
PT3M14S 
PT4H17M16S 
PT3M46S 
PT3H18M35S 
PT3H8M45S

更新:我试过了

WITH data AS (
  SELECT (duration) as time_str FROM x
)
SELECT 
  CASE
    WHEN time_str LIKE 'PT%H%M%S' THEN PARSE_TIME('PT%HH%MM%SS', time_str)
    WHEN time_str LIKE 'PT%M%S' THEN PARSE_TIME('PT%MM%SS', time_str)
    WHEN time_str LIKE 'PT%S' THEN PARSE_TIME('PT%SS', time_str)
    ELSE NULL
  END
FROM data

我得到错误：

格式字符“S”和字符串字符“H”之间不匹配

Answer 1

也许像这样的事情可以：

WITH data AS (
  SELECT * FROM UNNEST(['PT48S', 'PT43M40S', 'PT7M54S', 'PT3M14S', 'PT4H17M16S', 'PT3M46S', 'PT3H18M35S', 'PT3H8M45S']) as time_str
)
SELECT 
  CASE
    WHEN time_str LIKE 'PT%H%M%S' THEN PARSE_TIME('PT%HH%MM%SS', time_str)
    WHEN time_str LIKE 'PT%M%S' THEN PARSE_TIME('PT%MM%SS', time_str)
    WHEN time_str LIKE 'PT%S' THEN PARSE_TIME('PT%SS', time_str)
    ELSE NULL
  END
FROM data

Answer 2

下面考虑

select time_str,
  ( select parse_time('%H:%M:%S', string_agg(ifnull(lpad(val, 2, '0'), '00'), ':' order by offset))
    from unnest([
      regexp_extract(time_str, r'(\d+)H'), 
      regexp_extract(time_str, r'(\d+)M'), 
      regexp_extract(time_str, r'(\d+)S')
    ]) val with offset) time_time
from data           

如果应用于问题中的示例数据-输出是