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:

Weekday	Opening	Closing
0	00:10:00	00:20:00
1	00:10:00	00:20:00

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

Solution

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.