Convert string in HH:MM:SS format to seconds in Google BigQuery

I want to convert Time (for example Video duration) to duration in seconds

Solution:

#standardSQL
WITH `project.dataset.vid_length_table` AS (
  SELECT '1:54:55' videoLength UNION ALL
  SELECT '2:26' UNION ALL
  SELECT '146'
)
SELECT videoLength AS old_video_length,
  CASE
    WHEN REGEXP_CONTAINS(videoLength, r':\d\d:\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%T', videoLength), TIME '00:00:00', SECOND)
    WHEN REGEXP_CONTAINS(videoLength, r':\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%M:%S', videoLength), TIME '00:00:00', SECOND)
    ELSE SAFE_CAST(videoLength AS INT64)
  END AS video_length_converted
FROM `project.dataset.vid_length_table`   

with result

Row old_video_length    video_length_converted   
1   1:54:55             6895     
2   2:26                146  
3   146                 146  

Ryan-Dallas

posted on 01 Feb 21