Passing index expression with filter() to database

Viewed 24

I have PostgreSQL table table with lot of rows.
In order to make queries faster and to ensure UTC time zone, it indexed by timezone('utc'::text, t.closed_at)::date

for getting data I use following code, but it's slow

    t  <- tbl(wcon, 'table') 

today     <- as.character( as_date(Sys.time()- hours(1)))
# weekdays compare
wks_n <- 5
prev_weeks <- Sys.Date()-wks_n*7

lh_f <- t %>% 
  filter(closed_at >= prev_weeks,
         closed_at <= today) %>% 
  collect() 

How I can pass values to this index expression with filter() ?

As a faster workaround I use creating query like this

my_query <- paste0("select * from table t ", 
               "where timezone('utc'::text, t.closed_at)::date ",
               "between ", prev_weeks, " and ", today)
0 Answers
Related