Q:

Add postgres time interval using dplyr/dbplyr

I have a database connection in R and would like to implement the following filtering step---in Postgres---using dplyr (v0.5):
WHERE time1 < time2 - INTERVAL '30 minutes'

(see https://www.postgresql.org/docs/9.1/static/functions-datetime.html)
I tried the following (which is what I would do for POSIX objects) but received this error:
tbl(con, 'data') %>%
  filter(time1 < time2 - 30 * 60) %>%
  collect()
# ERROR: operator does not exist: timestamp without timezone - numeric

What's the correct way to do this?

A:

According to the SQL translation vignette:

Any function that dplyr doesn’t know how to convert is left as is.
  This means that database functions that are not covered by dplyr can
  be used directly via translate_sql().

But you cannot use %interval%, because your condition would not be syntactically correct in R:
time1 < time2 - %interval% "30 minutes"
# Error: unexpected SPECIAL in "time1 < time2 - %interval%"

Using interval is not better:
time1 < time2 - interval "30 minutes"
# Error: unexpected string constant in "time1 < time2 - interval "30 minutes""

But the following trick does work:
dbplyr::translate_sql(time1 < time2 %- interval% "30 minutes")
# <SQL> "time1" < "time2" - INTERVAL '30 minutes'

So this code should answer your question:
tbl(con, "data") %>%
  filter(time1 < time2 %- interval% "30 minutes") %>%
  collect

