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)