Convert tz in postgres with dbplyr

tidyverse
, , , ,
3

This is an interesting dbplyr trick, thanks for sharing.

In your place I would use the Postgres function timezone() to push the calculation to backend, but in doing that I would lose the ability to generate the code in summarise_all() and would have to either write the avg() for all columns by hand, or pull all the rows locally; neither of which is particularly efficient.

asdf <- dbGetQuery(con, "select timezone('CET', time) as modtime, * 
                         from my_table") %>%
  group_by(modtime) %>%
  summarise_all(funs(mean(.)))
1 Like