Thank you for the explanation! Here is why I am asking. I'm experimenting with a way to extend dbplyr sql translations. In particular here is an example of adding a dateadd function that translates to the correct sql on different database systems. This is a minimal example of what I'm thinking just to get the idea across. There are issues with it though (i.e. it only works with the Magrittr pipe).
Anyway if you have an idea of how to do this I would very much appreciate any suggestions.
Thanks!
dateadd <- function(date, number, interval = "day") {
dot <- get(".", envir = parent.frame())
sql <- switch (class(dot$src$con)[1],
"duckdb_connection" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
"redshift" = glue::glue("DATEADD({interval}, {number}, {date})"),
"oracle" = glue::glue("({date} + NUMTODSINTERVAL({number}, 'day'))"),
"postgresql" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
"sql server" = glue::glue("DATEADD({interval}, {number}, {date})"),
"spark" = glue::glue("date_add({date}, {number})"),
"sqlite" = glue::glue("CAST(STRFTIME('%s', DATETIME({date}, 'unixepoch', ({number})||' {interval}s')) AS REAL)"),
"bigquery" = glue::glue("DATE_ADD({date}, INTERVAL {number} {toupper(interval)})"),
"snowflake" = glue::glue('DATEADD({interval}, {number}, {date})'),
stop(glue::glue("Connection type {class(dot$src$con)[1]} is not supported!"))
)
dbplyr::sql(as.character(sql))
}
con <- DBI::dbConnect(duckdb::duckdb())
date_tbl <- dplyr::copy_to(con, data.frame(date1 = as.Date("1999-01-01")),
name = "tmpdate", overwrite = TRUE, temporary = TRUE)
library(magrittr)
# works
date_tbl %>%
dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year"))
#> # Source: SQL [1 x 2]
#> # Database: DuckDB 0.8.2-dev77 [root@Darwin 21.6.0:R 4.2.2/:memory:]
#> date1 date2
#> <date> <date>
#> 1 1999-01-01 2000-01-01
# fails
dplyr::mutate(date_tbl, date2 = !!dateadd("date1", 1, interval = "year"))
#> Error in get(".", envir = parent.frame()): object '.' not found
# fails
date_tbl |>
dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year"))
#> Error in get(".", envir = parent.frame()): object '.' not found
DBI::dbDisconnect(con, shutdown = TRUE)
Created on 2023-07-12 with reprex v2.0.2