Is it possible to change the timezone using dplyr/dbplyr syntax in the database? All my observations are stored in UTC, but I need it in CET for my group_by/summary processing and it is not very efficient for me to collect() the data and and do it locally. My current solution is not very "pretty".
Here's some pseudo code to illustrate the problem:
library(tidyverse)
con <- DBI::dbConnect()
df <- tbl(con, "my_table")
df %>%
mutate(datetime = lubridate::with_tz(datetime, "CET")) %>%
mutate(datetime = lubridate::floor_date(datetime, "days")) %>%
group_by(datetime) %>%
summarise_all(funs(mean(.))
If you have additional hints for working with datetime objects and timezones in postgres with dplyr it will be much appreciated.