CODEWITHSUNDEEP
google-bigquerysql-timestamp
mabiel
mabiel

Reputation: 105

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.

Upvotes: 0

Views: 252

Answers (2)

Jaytiger
Jaytiger

Reputation: 12274

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;

enter image description here

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.

enter image description here

Upvotes: 0

Shipra Sarkar
Shipra Sarkar

Reputation: 1485

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 : enter image description here

Upvotes: 0

Related Questions