Getting the database sysdate using dbplyr verb

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.

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)

1 Like

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?

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.