使用dplyr/dbplyr添加postgres时间间隔。

I have a database connection in R and would like to implement the following filtering step---in Postgres---using dplyr (v0.5):

我在R中有一个数据库连接，我想使用dplyr (v0.5)实现以下过滤步骤—在Postgres中—

WHERE time1 < time2 - INTERVAL '30 minutes'

(see https://www.postgresql.org/docs/9.1/static/functions-datetime.html)

(参见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:

我尝试了以下方法(这是我对POSIX对象所做的)，但是收到了这个错误:

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?

正确的做法是什么?

1 个解决方案

#1

According to the SQL translation vignette:

根据SQL翻译简介:

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().

任何dplyr不知道如何转换的函数都保留原样。这意味着dplyr不覆盖的数据库函数可以直接通过translate_sql()来使用。

But you cannot use %interval%, because your condition would not be syntactically correct in R:

但是不能使用%interval%，因为在R中，您的条件在语法上是不正确的:

time1 < time2 - %interval% "30 minutes"
# Error: unexpected SPECIAL in "time1 < time2 - %interval%"

Using interval is not better:

使用interval并不好:

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

#1