Bigquery iso8601 string to timestamp

Question

In my dataset I have a variable duration where there are 2 million rows of data in ISO 8601 format. Example format: PT21S or PT5M29S, PT1M16S

I'm having a problem converting this string variable to time. At the moment I'm using the query:

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

Through this query it is possible to distinguish whether the duration is in hours minutes or seconds. I would like to convert the string duration to time. I have used Regex and cast but without success. When I try to convert the string to time I get the errors:

"Invalid timestamp: '15S'"

"Invalid timestamp: '1M'

"Bad int64 value: PT11M16S"

The biggest problem I run into is that the the string is never the same.

Further string Examples:

PT48S
PT43M40S 
PT7M54S 
PT3M14S 
PT4H17M16S 
PT3M46S 
PT3H18M35S 
PT3H8M45S

Update: I tried

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

I got the error:

Mismatch between format character 'S' and string character 'H'

user_5221944 · Answer

Consider below

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

if applied to sample data in your question - output is

user_13473525 · Answer

Maybe something like this would be ok:

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

Bigquery iso8601 string to timestamp

2 Answers