BigQuery - day of week number where Monday = 1

3
votes

Is there a standard way to return "day of week number" (1-7) in ISO standard (Monday = 1) from a date in BigQuery using built in functions?

EXTRACT(DAYOFWEEK, <date>) returns a number using US format i.e. Sunday = 1, which doesn't work for my purpose.

I can create a custom function to calculate the ISO day of week number but I'm not sure whether I'm missing something built in that I can use instead.

2
google-bigquery
no, you are not missing anything - just go with custom function and optionally submit feature request - Mikhail Berlyant
Thanks - do you know where I should submit such a request for BigQuery? - Nathan Griffiths
You can submit feature request at Issue Tracker . Also consider voting up answer if it was helpful :o) - Mikhail Berlyant

2 Answers

4
votes

You can convert to ISO-standard (Monday = 1) using modulo arithmetic:

MOD(EXTRACT(DAYOFWEEK FROM <date>) + 5, 7) + 1
1
votes

Below is example of such function - just one of many options you can come with 

#standardSQL
CREATE TEMP FUNCTION ISODAYOFWEEK(day DATE) AS ((
  SELECT IF(weekday = 0, 7, weekday) 
  FROM UNNEST([EXTRACT(DAYOFWEEK FROM day) - 1]) weekday 
));