Extracting timestamp from string big query standard sql

Question

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.

Shipra Sarkar · Answer 1 · 2022-10-19T11:46:42.573

For your requirement, split can be used. You can check out this below sample query.

declare value String DEFAULT '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';
    
select weekday,
concat('00:',trim(opening))opening,
concat('00:',trim(closing))closing
from(
select *,
SPLIT(trim(c), ':')[OFFSET(0)] weekDay,
SPLIT(trim(c), '-')[OFFSET(0)] t1,
Split(SPLIT(trim(c), '-')[OFFSET(0)],':')[OFFSET(1)] t2,
Split(SPLIT(trim(c), '-')[OFFSET(0)],':')[OFFSET(2)] t3,
concat(Split(SPLIT(trim(c), '-')[OFFSET(0)],':')[OFFSET(1)],
':',Split(SPLIT(trim(c), '-')[OFFSET(0)],':')[OFFSET(2)])opening, # by concatinating t2 and t3
SPLIT(trim(c), '-')[OFFSET(1)] closing
from (
  SELECT * from
  UNNEST(SPLIT(value)) c ) 
)

Output :

score 0 · Accepted Answer · answered Oct 19 '22 at 15:05

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.

Extracting timestamp from string big query standard sql

2 Answers2