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\nWITH `project.dataset.vid_length_table` AS (\n  SELECT '1:54:55' videoLength UNION ALL\n  SELECT '2:26' UNION ALL\n  SELECT '146'\n)\nSELECT videoLength AS old_video_length,\n  CASE\n    WHEN REGEXP_CONTAINS(videoLength, r':\d\d:\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%T', videoLength), TIME '00:00:00', SECOND)\n    WHEN REGEXP_CONTAINS(videoLength, r':\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%M:%S', videoLength), TIME '00:00:00', SECOND)\n    ELSE SAFE_CAST(videoLength AS INT64)\n  END AS video_length_converted\nFROM `project.dataset.vid_length_table`   \n

with result

Row old_video_length    video_length_converted   \n1   1:54:55             6895     \n2   2:26                146  \n3   146                 146  \n

Ryan-Dallas

posted on