[dbplyr translation] `as.POSIXct` doesn't respect timezone.

[cy-james-lee]

It seems timezone differences are dealt correctly if datetime vectors are used directly (converts to UTC first), but if as.POSIXct is used interactively, timezone is ignored.

library(duckdb, quietly = TRUE)
library(dplyr, quietly = TRUE, warn.conflicts = FALSE)

conn1 <- dbConnect(duckdb(), ":memory:")
tbl1 <-
  tbl(
    conn1,
    sql("SELECT now() AS now")
  ) |> 
  mutate(
    now_txt = as.character(now),
    compare1 = now > as.POSIXct("2025-03-01 18:00:00"), # ignores tz
    compare2 = now > !!as.POSIXct("2025-03-01 18:00:00") # respects tz
  )

tbl1
#> # Source:   SQL [?? x 4]
#> # Database: DuckDB v1.2.0 [root@Darwin 24.3.0:R 4.4.2/:memory:]
#>   now                 now_txt                    compare1 compare2
#>   <dttm>              <chr>                      <lgl>    <lgl>   
#> 1 2025-03-01 22:13:17 2025-03-01 22:13:17.795+00 TRUE     FALSE

tbl1 |>  
   show_query()
#> <SQL>
#> SELECT
#>   q01.*,
#>   CAST(now AS TEXT) AS now_txt,
#>   now > CAST('2025-03-01 18:00:00' AS TIMESTAMP) AS compare1,
#>   now > '2025-03-01 23:00:00'::timestamp AS compare2
#> FROM (SELECT now() AS now) q01

^{Created on 2025-03-01 with reprex v2.1.1}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.4.2 (2024-10-31)
#>  os       macOS Sequoia 15.3.1
#>  system   aarch64, darwin20
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       America/Indiana/Indianapolis
#>  date     2025-03-01
#>  pandoc   3.2 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/aarch64/ (via rmarkdown)
#>  quarto   1.6.42 @ /usr/local/bin/quarto
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  blob          1.2.4   2023-03-17 [1] CRAN (R 4.4.0)
#>  cli           3.6.4   2025-02-13 [1] CRAN (R 4.4.1)
#>  DBI         * 1.2.3   2024-06-02 [1] CRAN (R 4.4.0)
#>  dbplyr        2.5.0   2024-03-19 [1] CRAN (R 4.4.0)
#>  digest        0.6.37  2024-08-19 [1] CRAN (R 4.4.1)
#>  dplyr       * 1.1.4   2023-11-17 [1] CRAN (R 4.4.0)
#>  duckdb      * 1.2.0   2025-02-21 [1] CRAN (R 4.4.1)
#>  evaluate      1.0.3   2025-01-10 [1] CRAN (R 4.4.1)
#>  fastmap       1.2.0   2024-05-15 [1] CRAN (R 4.4.0)
#>  fs            1.6.5   2024-10-30 [1] CRAN (R 4.4.1)
#>  generics      0.1.3   2022-07-05 [1] CRAN (R 4.4.0)
#>  glue          1.8.0   2024-09-30 [1] CRAN (R 4.4.1)
#>  htmltools     0.5.8.1 2024-04-04 [1] CRAN (R 4.4.0)
#>  knitr         1.49    2024-11-08 [1] CRAN (R 4.4.1)
#>  lifecycle     1.0.4   2023-11-07 [1] CRAN (R 4.4.0)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.4.0)
#>  pillar        1.10.1  2025-01-07 [1] CRAN (R 4.4.1)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.4.0)
#>  purrr         1.0.4   2025-02-05 [1] CRAN (R 4.4.1)
#>  R6            2.6.1   2025-02-15 [1] CRAN (R 4.4.1)
#>  reprex        2.1.1   2024-07-06 [1] CRAN (R 4.4.0)
#>  rlang         1.1.5   2025-01-17 [1] CRAN (R 4.4.1)
#>  rmarkdown     2.29    2024-11-04 [1] CRAN (R 4.4.1)
#>  rstudioapi    0.17.1  2024-10-22 [1] CRAN (R 4.4.1)
#>  sessioninfo   1.2.3   2025-02-05 [1] CRAN (R 4.4.1)
#>  tibble        3.2.1   2023-03-20 [1] CRAN (R 4.4.0)
#>  tidyselect    1.2.1   2024-03-11 [1] CRAN (R 4.4.0)
#>  utf8          1.2.4   2023-10-22 [1] CRAN (R 4.4.0)
#>  vctrs         0.6.5   2023-12-01 [1] CRAN (R 4.4.0)
#>  withr         3.0.2   2024-10-28 [1] CRAN (R 4.4.1)
#>  xfun          0.51    2025-02-19 [1] CRAN (R 4.4.1)
#>  yaml          2.3.10  2024-07-26 [1] CRAN (R 4.4.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/library
#>  * ── Packages attached to the search path.
#> 
#> ──────────────────────────────────────────────────────────────────────────────

[krlmlr]

Thanks. Something weird is going on indeed:

library(duckdb, quietly = TRUE)
library(dplyr, quietly = TRUE, warn.conflicts = FALSE)

time <- format(Sys.time())
time
#> [1] "2025-03-07 21:41:35"

conn1 <- dbConnect(duckdb(), ":memory:")
tbl1 <-
  tbl(
    conn1,
    sql("SELECT now() AS now")
  ) |>
  mutate(
    now_txt = as.character(now),
    compare1 = now > as.POSIXct(time), # ignores tz
    compare2 = now > !!as.POSIXct(time) # respects tz
  )

tbl1
#> # Source:   SQL [?? x 4]
#> # Database: DuckDB v1.2.1 [root@Darwin 24.3.0:R 4.4.2/:memory:]
#>   now                 now_txt                   compare1 compare2
#>   <dttm>              <chr>                     <lgl>    <lgl>   
#> 1 2025-03-07 20:41:35 2025-03-07 20:41:35.52+00 FALSE    TRUE

tbl1 |>
  show_query()
#> <SQL>
#> SELECT
#>   q01.*,
#>   CAST(now AS TEXT) AS now_txt,
#>   now > CAST('2025-03-07 21:41:35' AS TIMESTAMP) AS compare1,
#>   now > '2025-03-07 20:41:35'::timestamp AS compare2
#> FROM (SELECT now() AS now) q01

^{Created on 2025-03-07 with reprex v2.1.1}

I think this needs to be fixed in dbplyr because I don't need to load duckdb to replicate this. I wonder where the inconsistency between escape() and !! comes from. Can you please raise there?

library(dplyr, quietly = TRUE, warn.conflicts = FALSE)
library(dbplyr, quietly = TRUE, warn.conflicts = FALSE)

time <- Sys.time()
time
#> [1] "2025-03-07 21:53:05 CET"
time_fmt <- format(time)
time_fmt
#> [1] "2025-03-07 21:53:05"

# simulate_duckdb()
con <- structure(list(), class = c("duckdb_connection", "TestConnection", "DBIConnection"))
tbl1 <-
  lazy_frame(
    con = con,
    a = 1
  ) |>
  mutate(
    time = as.POSIXct(time_fmt)
  )

tbl1 |>
  show_query()
#> <SQL>
#> SELECT `df`.*, CAST('2025-03-07 21:53:05' AS TIMESTAMP) AS `time`
#> FROM `df`

base_scalar$as.POSIXct
#> function (x) 
#> {
#>     sql_expr(cast(!!x %as% !!type))
#> }
#> <bytecode: 0x110d62190>
#> <environment: 0x110d5d208>

escape(time, con = con)
#> <SQL> '2025-03-07T20:53:05Z'

^{Created on 2025-03-07 with reprex v2.1.1}

CC @mgirlich.

[cy-james-lee]

Isn't escape() working just like !!? Your time "2025-03-07 21:53:05" is changed to "2025-03-07T20:53:05Z".

It's my understanding that if I double bang !!as.POSIXct(time) in dplyr, then it's same as passing a variable like so:

 time_in_POSIXct <- as.POSIXct(time)

# these should be same
tbl |>
  mutate(
    col1 = !!as.POSIXct(time),
    col2 = time_in_POSIXct,
  )

I thought the problem was how as.POSIXct translation was being handled with regards to the database timezone. Since DuckDB is only using UTC, all date/datetime objects with timezone from R need to be converted to UTC for SQL. It seems if you pass POSIXct objects, they get converted to UTC for DuckDB. But if you are converting a text with as.POSIXct in Tidyverse functions, the SQL becomes text as is, ignoring whatever timezone as.POSIXct would have given it (which is local timezone by default).