filter to just recent datetimes with dbplyr

[nyoungblut]

I've seen post for how to filter records of a specific date using dbplyr, but can one use dbplyr to filter to say, just the records from the past X hours? It would be great if the dbplyr had more docs on working with datetimes...

[mishabalyasin]

dbplyr is a front-end for whatever RDBMS you are using, so it's difficult to have a generic advice. In general, you'd probably simply do something like this:

tbl %>%
     dplyr::filter(datetime_column > "2020-03-28")

and it'll work.

Do you have a specific problem in mind though?

[nyoungblut]

Thanks for the quick feedback! I'm working with a sqlite3 database. My time entries are TEXT formatted as "2020-03-29 14:35:01". I'd like to select only records that were added in the past X hours. Right now, I'm just skipping dbplyr and using sql directly:

sql = "SELECT * FROM my_table WHERE time > DATETIME('now', 'localtime', '-4 hours')"
df = DBI::dbGetQuery(conn, sql)