Create new date (week) columns the dplyr-way so that SQL can translate properly

tidyverse
, ,
1

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?

2

solution:

tibble(date=as_datetime("2020-10-21 00:00:00")) %>%
mutate(`specific_week` = sql(DATE_PART('week',  date)))
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.