Convert tz in postgres with dbplyr

tidyverse
, , , ,
2

UPDATE2: I used the timezone function that @jlacko suggested, but in a mutate statement and now it works as expected. Thanks, @jlacko.

The code below is the best solution I could come up with. The code works with my postgres DB. It was the mutate statement with the dbplyr::sql() function that was the hardest part, because I didn't know how to get dplyr to translate it:

library(tidyverse)

con <- DBI::dbConnect()

df <- tbl(con, "my_table")
df %>%
  mutate(datetime = timezone('CET', datetime)) %>%
  mutate(datetime = date_trunc("day", datetime)) %>%
  group_by(datetime) %>%
  summarise_all(funs(mean(.))
4 Likes