Extracting timestamp from string big query standard sql

I have a column of store opening hours in STRING format where one column*row entry looks like this:

Monday: 10:00 - 20:00, Tuesday: 10:00 - 20:00, Wednesday: 10:00 - 20:00, Thursday: 10:00 - 20:00, Friday: 10:00 - 20:00, Saturday: 10:00 - 20:00, Sunday: 11:00 - 18:00

. I would like to transform this entry into several column*row entries such like this:

The timestamp format I need in order to obtain foottraffic for stores at certain hours of the day.

Consider below option as well,

• weekday in your expected output is not clear to me. If it's just an order of appearance, I thinks below query is enough for your purpose.

WITH sample_data AS (
  SELECT 'Monday: 10:00 - 20:00, Tuesday: 10:00 - 20:00, Wednesday: 10:00 - 20:00, Thursday: 10:00 - 20:00, Friday: 10:00 - 20:00, Saturday: 10:00 - 20:00, Sunday: 11:00 - 18:00' str
)
SELECT offset AS weekday,
       PARSE_TIME('%H:%M', SPLIT(RIGHT(day, 13), ' - ')[OFFSET(0)]) opening,
       PARSE_TIME('%H:%M', SPLIT(RIGHT(day, 13), ' - ')[OFFSET(1)]) closing,
  FROM sample_data, UNNEST(SPLIT(str, ', ')) day WITH offset;

but if you want an explicit order of weekday, you can use this instead.

SELECT offset AS weekday,
       PARSE_TIME('%H:%M', SPLIT(RIGHT(day, 13), ' - ')[OFFSET(0)]) opening,
       PARSE_TIME('%H:%M', SPLIT(RIGHT(day, 13), ' - ')[OFFSET(1)]) closing,
  FROM sample_data, UNNEST(SPLIT(str, ', ')) day
  JOIN UNNEST(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']) w WITH offset
    ON SPLIT(day, ':')[OFFSET(0)] = w;

• 10:00 - 20:00 seems to be between 10 to 20 o'clock, not 10 minute to 20 minute, so I've used '%H:%M' format string. If you really mean it as minutes, you can use '%M:%S' instead, then output will be like below.