0

I'm trying to use bigrquery and dbplyr to get the week of the year that a date corresponds to (i.e. the same as lubridate::week(), i.e. 

library(lubridate)
library(dbplyr)
library(bigrquery)

week("2015-08-11")   
# [1] 32

but I am using bigrquery and dbplyr

What I've tried so far

Using lubridate::week() I see

transactions %>% 
  select(item, date) %>% 
  mutate(week = week(date)) %>%
  collect()
Error: Function not found: week at [1:30] [invalidQuery]

So I attempted this home made solution

transactions %>% 
  select(item, date) %>%
  mutate(week = strftime(date, format = "%V")) %>% 
  collect()

Error: Syntax error: Expected ")" but got keyword AS at [1:54] [invalidQuery]
In addition: Warning message:
  Named arguments ignored for SQL strftime

as well as another (fairly ugly) home made solution

transactions %>% 
  select(item, date) %>% 
  mutate(week = as.numeric((as.Date(date) - as.Date(paste0(substr(date, 1, 4), "-01-01"))), units="days") %/% 7) %>% 
  collect()

Error in as.numeric((as.Date(date) - as.Date(paste0(substr(date, 1,  : 
unused argument (units = "days")

but I cannot seem to find a way to get the week number using bigquery and dbplyr

stevec
  • 41,291
  • 27
  • 223
  • 311

1 Answers1

2

I cannot seem to find a way to get the week number using bigquery

Looks like you are looking for below BigQuery Standard SQL function 

EXTRACT(WEEK FROM date)

You can use WEEK or WEEK(< WEEKDAY>) or ISOWEEK

See more about date part here https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • 1
    `dbplyr` translates from standard `dplyr` commands to database language (in your case BigQuery). No translation for `lubridate::week()` appears to exist because it is not part of the `dplyr` core. See https://stackoverflow.com/questions/55348039/how-to-give-dplyr-a-sql-query-and-have-it-return-a-remote-tbl-object for a way to take advantage of this. – Simon.S.A. Dec 06 '19 at 02:42
  • 1
    @Simon.S.A. - i think you meant to comment on question, not on my answer :o) or you can just post your answer – Mikhail Berlyant Dec 06 '19 at 02:47