wrong format of Posixt datetime by DBI::dbQuoteLiteral

[philibe]

Since 1.7.0 glue_sql uses DBI::dbQuoteLiteral() (see tidyverse/glue#321 : "glue ≥ 1.7 : wrong format of Posixt datetime by glue_sql() ")

Here is below the reprex for DBI 1.2.1 (but this is the same issue for DBI 1.1.3).

• In general ANSI SQL the answer should be : '2023-11-16 14:52:45'
• But DBI::dbQuoteLiteral() gives '20231116145245'

For information I use:

• DBI::dbConnect(odbc::odbc(),.connection_string=choosen_RDBMS_connection, encoding = database_encoding) on Linux Ubuntu (see session_info below)
• and SQL Server :
  • Database type and version: SQL Server 2005 9.00.3042.00
  • OS: Windows 7 SP1 32 bits

library(DBI)
datas<-structure(
  list(
    mydatetime_posixt = structure(1700146365, 
                                  tzone = "UTC", 
                                  class = c("POSIXct",  "POSIXt")
    ), 
    mydate = structure(19676, class = "Date")
  ), 
  row.names = c(NA,-1L), 
  class = c("tbl_df", "tbl", "data.frame")
)

DBI::dbQuoteLiteral(conn=DBI::ANSI(),  datas$mydatetime_posixt)
#> <SQL> '20231116145245'

^{Created on 2024-02-23 with reprex v2.1.0}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.3.2 (2023-10-31)
#>  os       Ubuntu 22.04.3 LTS
#>  system   x86_64, linux-gnu
#>  ui       X11
#>  language en
#>  collate  fr_FR.UTF-8
#>  ctype    fr_FR.UTF-8
#>  tz       Europe/Paris
#>  date     2024-02-23
#>  pandoc   3.1.1 @ /usr/lib/rstudio-server/bin/quarto/bin/tools/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  cli           3.6.2   2023-12-11 [1] CRAN (R 4.3.2)
#>  DBI         * 1.2.1   2024-01-12 [1] CRAN (R 4.3.2)
#>  digest        0.6.34  2024-01-11 [1] CRAN (R 4.3.2)
#>  evaluate      0.23    2023-11-01 [1] CRAN (R 4.3.2)
#>  fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.3.2)
#>  fs            1.6.3   2023-07-20 [1] CRAN (R 4.3.2)
#>  glue          1.7.0   2024-01-09 [1] CRAN (R 4.3.2)
#>  htmltools     0.5.7   2023-11-03 [1] CRAN (R 4.3.2)
#>  knitr         1.45    2023-10-30 [1] CRAN (R 4.3.2)
#>  lifecycle     1.0.4   2023-11-07 [1] CRAN (R 4.3.2)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.3.2)
#>  purrr         1.0.2   2023-08-10 [1] CRAN (R 4.3.2)
#>  R.cache       0.16.0  2022-07-21 [1] CRAN (R 4.3.2)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.3.2)
#>  R.oo          1.26.0  2024-01-24 [1] CRAN (R 4.3.2)
#>  R.utils       2.12.3  2023-11-18 [1] CRAN (R 4.3.2)
#>  reprex        2.1.0   2024-01-11 [1] CRAN (R 4.3.2)
#>  rlang         1.1.3   2024-01-10 [1] CRAN (R 4.3.2)
#>  rmarkdown     2.25    2023-09-18 [1] CRAN (R 4.3.2)
#>  rstudioapi    0.15.0  2023-07-07 [1] CRAN (R 4.3.2)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.3.2)
#>  styler        1.10.2  2023-08-29 [1] CRAN (R 4.3.2)
#>  vctrs         0.6.5   2023-12-01 [1] CRAN (R 4.3.2)
#>  withr         3.0.0   2024-01-16 [1] CRAN (R 4.3.2)
#>  xfun          0.41    2023-11-01 [1] CRAN (R 4.3.2)
#>  yaml          2.3.5   2022-02-21 [2] CRAN (R 4.1.2)
#> 
#>  [1] /usr/local/lib/R/site-library
#>  [2] /usr/lib/R/site-library
#>  [3] /usr/lib/R/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

[krlmlr]

Thanks. It seems indeed that DBI has this wrong, I don't remember why I chose this format.

Can you confirm that YYYY-MM-DD HH:MI:SS would be correct, as described in https://www.coginiti.co/tutorials/intermediate/sql-date-formats/ and various other sources?

[philibe]

TDLR

YYYY-MM-DD HH:MI:SS should be correct, as described in th url, and I've encountered on many RDBMS, but parameters should be better.

detail

I don't remember why I chose this format.

• It seems that you have used basic ISO 8601 format for datetime (yyyyMMddThhmmss) which is not supported by many RDBMS without conversion. :

  • https://stackoverflow.com/questions/813238/tsql-datetime-iso-8601/62674162#62674162
  • https://www.iso.org/iso-8601-date-and-time-format.html

• For SQL Server there is yyyy-MM-ddTHH:mm:ssZ but only from SQL Server 12, before it was yyyy-mm-dd hh:mi:ss

  • https://stackoverflow.com/questions/813238/tsql-datetime-iso-8601/35492984#35492984

• For my SQL Server I use:

  • select convert(varchar,getdate(),120) : 2024-02-23 16:14:03
    • The format are here https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql with 120 =

    • ODBC canonical yyyy-mm-dd hh:mi:ss (24-hour)

IMHO you should add a parameter to choose format for strftime but use by default yyyy-mm-dd hh:mi:ss and UTC, in others words to not put a hard coded constant :)

dbQuoteLiteral_DBIConnection <- function(conn, x, ..., format ='yyyy-mm-dd hh:mi:ss', tz='UTC') {
  (...)
  if (inherits(x, "POSIXt")) {
    return(dbQuoteString(
      conn,
      strftime(as.POSIXct(x), format = format , tz =tz)
    ))
  (...)
}

[philibe]

YYYY-MM-DD HH:MI:SS should be correct, as described in th url, and I've encountered on many RDBMS, but parameters should be better.

Or format in function of database. In original issue tidyverse/glue#321 (comment) on glue, the connection is sent, and conversion are expected to be done in function of the database.

Except maybe in this case if format = 'yyyy-mm-dd hh:mi:ss' is supported by 100% of RDBMS.

[krlmlr]

I forgot about time zones. Is there a universally supported format to specify at least the UTC offset? At least this works in Postgres:

con <- RPostgres::postgresDefault()

DBI::dbGetQuery(con, "SELECT '2024-04-01 19:09:34+0200'::timestamptz")
#>           timestamptz
#> 1 2024-04-01 17:09:34

^{Created on 2024-04-01 with reprex v2.1.0}

(It is correct because the default timezone_out is UTC.)

Passing a format to dbQuoteLiteral() defeats its purpose. Databases that need a different syntax will have to implement their own dbQuoteLiteral() method.

In DBI, I'd like to implement something that works reasonably well for all databases. But it has to be correct, or fail.

[philibe]

fix: dbQuoteLiteral() uses the format "%Y-%m-%d %H:%M:%S%z" which is understood by more databases

(merged Jun 26, 2024 in https://github.com/r-dbi/DBI/releases/tag/v1.2.3.9002)

Thanks.

but

For SQL Server there is yyyy-MM-ddTHH:mm:ssZ but only from SQL Server 12, before it was yyyy-mm-dd hh:mi:ss

So, for readers, for my old database I use:

library(dplyr)
library(lubridate)

(
  mydatas
  %>% dplyr::mutate(
    dplyr::across(
      tidyselect::where(lubridate::is.POSIXt),
      function (x) { base::format(x, format  =  "%Y-%m-%d %H:%M:%S")}
    )
  )
)

but I understand the fix with timezone for normal recent RDBMS :)