duckplyr support for duckdb date functions

[Richard_Hunt]

Hi!

I am trying to use duckDB via the "duckplyr" package. I need access to some date logic - for instance the ability to add or subtract 6 months from a date - and do so very accurately (i.e. subtracting 180 days is not sufficient).

I see that duckDB does have support for this - Understanding DATE_ADD() in DuckDB - but I can't see how to use these functions from within duckplyr, despite looking through the documentation and the vignettes.

Any help would be greatly appreciated thanks!

Richard.

[Richard_Hunt]

@Margusl

Hi!

I would like to sincerely thank you for the comprehensive email below. I see that you actually deleted the comment from the thread I started but I found this very helpful and I was able to figure out a lot of how to do what I needed to do!

There have been no other replies so I will just mark this as closed now.

Thanks again!

[Richard_Hunt]

I would like to thank member @margusl for the comprehensive reply I received.

For some reason they deleted their response after originally posting it - I am not sure why - if there were errors of some sort which were found - then I could not see them.

Since I don't know why the original post was deleted, so I won't re-post here but I am happy to supply the information I was given to anyone who contacts me.

Best regards and thanks again!
Richard

[margusl]

@Richard_Hunt , glad it helped and you are more than welcome to re-post it.

I deleted it as I based my answer on dbplyr and only then realised you were actually asking about duckplyr, which currently provides different set of translations and does not pass through DuckDB calls like dbplyr does. I also considered this more like "hiding", waiting if someone could come up with a nice duckplyr solution. But apparently the time window to undelete my own replies here is limited and I can't recover it myself anymore.

[Richard_Hunt]

Here is the original reply from @margusl - with thanks again!

For adding days, you can just call date_add(date_x, 1L) . To add other intervals, you could use lubridate translations, e.g date_add(date_x, weeks(1)) , there are also few clock package translations available like add_years(date_x, 1). Or you could just include some escaped SQL: date_add(date_x, sql("INTERVAL 1 MONTH")) .

library("duckdb")
library("dplyr", warn.conflicts = FALSE)

(dates <- tibble(date_x = seq(as.Date("2025-01-01"), by = "month", length.out = 12)))
#> # A tibble: 12 × 1
#>    date_x    
#>    <date>    
#>  1 2025-01-01
#>  2 2025-02-01
#>  3 2025-03-01
#>  4 2025-04-01
#>  5 2025-05-01
#>  6 2025-06-01
#>  ...
 
con <- dbConnect(duckdb())
duckdb_register(con, "dates", dates)
dates_tbl <- 
  tbl(con, "dates") |>
  mutate(
    add_day   = date_add(date_x, 1L), # no translation
    add_week  = date_add(date_x, weeks(1)), # lubridate::weeks() translation
    add_month = date_add(date_x, sql("INTERVAL 1 MONTH")), #no translation + escape sql
    add_years = add_years(date_x, 1) # clock::add_years() translation
  )

Resulting query:

show_query(dates_tbl)
#> <SQL>
#> SELECT
#>   dates.*,
#>   date_add(date_x, 1) AS add_day,
#>   date_add(date_x, TO_DAYS(7 * CAST(1.0 AS INTEGER))) AS add_week,
#>   date_add(date_x, INTERVAL 1 MONTH) AS add_month,
#>   DATE_ADD(date_x, INTERVAL (1.0) year) AS add_years
#> FROM dates

Results:

dates_tbl
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB v1.2.0 [margus@Windows 10 x64:R 4.4.3/:memory:]
#>    date_x     add_day    add_week            add_month          
#>    <date>     <date>     <dttm>              <dttm>             
#>  1 2025-01-01 2025-01-02 2025-01-08 00:00:00 2025-02-01 00:00:00
#>  2 2025-02-01 2025-02-02 2025-02-08 00:00:00 2025-03-01 00:00:00
#>  3 2025-03-01 2025-03-02 2025-03-08 00:00:00 2025-04-01 00:00:00
#>  4 2025-04-01 2025-04-02 2025-04-08 00:00:00 2025-05-01 00:00:00
#>  5 2025-05-01 2025-05-02 2025-05-08 00:00:00 2025-06-01 00:00:00
#>  6 2025-06-01 2025-06-02 2025-06-08 00:00:00 2025-07-01 00:00:00
#>  7 2025-07-01 2025-07-02 2025-07-08 00:00:00 2025-08-01 00:00:00
#>  8 2025-08-01 2025-08-02 2025-08-08 00:00:00 2025-09-01 00:00:00
#>  9 2025-09-01 2025-09-02 2025-09-08 00:00:00 2025-10-01 00:00:00
#> 10 2025-10-01 2025-10-02 2025-10-08 00:00:00 2025-11-01 00:00:00
#> # 
ℹ more rows
#> # 
ℹ 1 more variable: add_years <dttm>

dbDisconnect(con)

---

And tracking down available translations is indeed bit messy, more so if you plan use your code with different backends. I don't believe there's actual up to date list of translations available in docs (everyone: you are more than welcome to correct me on this), so perhaps use backend source for reference:

github.com

duckdb/duckdb-r/blob/main/R/backend-dbplyr__duckdb_connection.R#L276

Or its test source, it is more compact and somewhat easier to follow:

github.com

IoannaNika/duckdb-r/blob/main/tests/testthat/test-backend-dbplyr__duckdb_connection.R#L119

expect_error(translate(quarter(x, type = "other")))

expect_error(translate(quarter(x, fiscal_start = 2)))

expect_equal(translate(month(x, label = FALSE)), sql(r"{EXTRACT(MONTH FROM x)}"))

expect_equal(translate(month(x, label = TRUE)), sql(r"{STRFTIME(x, '%b')}"))

expect_equal(translate(month(x, label = TRUE, abbr = FALSE)), sql(r"{STRFTIME(x, '%B')}"))

expect_equal(translate(qday(x)), sql(r"{DATE_DIFF('DAYS', DATE_TRUNC('QUARTER', CAST((x) AS DATE)), (CAST((x) AS DATE) + INTERVAL '1 DAY'))}"))

expect_equal(translate(wday(x)), sql(r"{EXTRACT('dow' FROM CAST(x AS DATE) + 0) + 1}"))

expect_equal(translate(wday(x, week_start = 4)), sql(r"{EXTRACT('dow' FROM CAST(x AS DATE) + 3) + 1}"))

expect_equal(translate(wday(x, label = TRUE)), sql(r"{STRFTIME(x, '%a')}"))

expect_equal(translate(wday(x, label = TRUE, abbr = FALSE)), sql(r"{STRFTIME(x, '%A')}"))

expect_equal(translate(seconds(x)), sql(r"{TO_SECONDS(CAST(x AS BIGINT))}"))

expect_equal(translate(minutes(x)), sql(r"{TO_MINUTES(CAST(x AS BIGINT))}"))

expect_equal(translate(hours(x)), sql(r"{TO_HOURS(CAST(x AS BIGINT))}"))

expect_equal(translate(days(x)), sql(r"{TO_DAYS(CAST(x AS INTEGER))}"))

expect_equal(translate(weeks(x)), sql(r"{TO_DAYS(7 * CAST(x AS INTEGER))}"))

expect_equal(translate(months(x)), sql(r"{TO_MONTHS(CAST(x AS INTEGER))}"))

expect_equal(translate(years(x)), sql(r"{TO_YEARS(CAST(x AS INTEGER))}"))

expect_equal(translate(floor_date(x, "month")), sql(r"{DATE_TRUNC('month', x)}"))

expect_equal(translate(floor_date(x, "week")), sql(r"{CAST(x AS DATE) - CAST(EXTRACT('dow' FROM CAST(x AS DATE) + 0) AS INTEGER)}"))

expect_equal(translate(floor_date(x, "week", week_start = 1)), sql(r"{DATE_TRUNC('week', x)}"))

expect_equal(translate(floor_date(x, "week", week_start = 4)), sql(r"{CAST(x AS DATE) - CAST(EXTRACT('dow' FROM CAST(x AS DATE) + 3) AS INTEGER)}"))