Getting the database sysdate using dbplyr verb

[MattP]

Quick question... using the standard dbplyr verbs, how can I access and use the database's sysdate value, such as for filtering, etc.?

As an example, how do I do, without doing raw sql (but with select/filter/mutate verbs) do:

select sysdate from dual

Trying this:

dual_tbl <-
    tbl(con, "dual")

dual_tbl %>%
    select(sysdate)

#> Error: Can't subset columns that don't exist.
#> x Column `sysdate` doesn't exist.

[edgararuiz]

Hi, the today() function has been masked in dbplyr to pull the DB's date. You will need to call lubridate so that R recognizes the function call. Here is an example:

library(RSQLite)
library(DBI)
library(lubridate)
library(dplyr)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

mtcars_db <- copy_to(con, mtcars)

mtcars_db %>% 
  mutate(x = today()) %>% 
  select(x) %>% 
  head(1)
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.36.0 [:memory:]
#>   x         
#>   <chr>     
#> 1 2021-10-18

^{Created on 2021-10-18 by the reprex package (v2.0.1)}

[MattP]

@edgararuiz

Hi, the today() function has been masked in dbplyr to pull the DB's date. You will need to call lubridate so that R recognizes the function call. Here is an example:

Thanks edgararuiz! This covers most of what I need. The sysdate sql keyword actually returns a timestamp, not a datestamp. So I can check at the date level, but at the timestamp level. Does something for that exist?

[edgararuiz]

Yup, now() should return the DB's current date-time