I have a table in a PostgresSQL database. This table has a datetime column with observations like 2020-10-21 00:00:00 and i want to generate two new columns from this datetime observations with dplyr namely current_calendar_week and week_of_that_specific_date
The usually way, aside from databases, would be with Sys.Date(), isoweek() or week() from the lubridate package:
tibble(date=as_datetime("2020-10-21 00:00:00")) %>%
mutate(`current_week` = isoweek(Sys.Date()), `specific_week` = isoweek(date))
However these functions cannot be translated to SQL properly. So my question is how would i correctly mutate these new columns with dplyr so that there is an equivalent SQL translation?