odbc
odbc copied to clipboard
Published
20 hours ago •
r-dbi
r-dbi
POSIXct losing precision on insert - Oracle
Issue Description and Expected Result
This is the same issue as #208 but for Oracle. Milliseconds are lost when inserted. The same code works with the ROracle package.
Database
Oracle Database 19c Enterprise Edition
Reproducible Example
options(digits.secs = 6)
df <- data.frame(DATETIME = Sys.time(), stringsAsFactors = FALSE)
library(odbc)
# the time has milliseconds in R
print(df)
# DATETIME
# 1 2020-09-19 22:51:55.485072
# odbc connection
con <- DBI::dbConnect(odbc::odbc(),
driver = "Oracle18",
uid = "username",
pwd = "password",
dbq = "database",
timezone = Sys.timezone(),
timezone_out = Sys.timezone())
DBI::dbExecute(con, "create table test_table (datetime timestamp(6))")
# insert data
DBI::dbExecute(con, "insert into test_table (datetime) values (:1)", params = df)
# the time does not have milliseconds when read back from Oracle
DBI::dbGetQuery(con, "select * from test_table")
# DATETIME
# 1 2020-09-19 22:58:03
DBI::dbExecute(con, "drop table test_table")
library(ROracle)
Sys.setenv(TZ = "EST5EDT",
ORA_SDTZ = "EST5EDT")
# ROracle connection
con <- ROracle::dbConnect(ROracle::Oracle(),
username = "username",
password = "password",
dbname = "database")
DBI::dbExecute(con, "create table test_table (datetime timestamp(6))")
# insert data
DBI::dbExecute(con, "insert into test_table (datetime) values (:1)", data = df)
DBI::dbCommit(con)
# the time does have milliseconds when read back from Oracle
DBI::dbGetQuery(con, "select * from test_table")
# DATETIME
# 1 2020-09-19 22:58:03.187895
DBI::dbExecute(con, "drop table test_table")
Session Info
devtools::session_info()
─ Session info ───────────────────────────────────────────────────────────────────────────────────────────
setting value
version R version 3.6.0 (2019-04-26)
os Red Hat Enterprise Linux
system x86_64, linux-gnu
ui RStudio
language (EN)
collate en_US.UTF-8
ctype en_US.UTF-8
tz America/New_York
date 2020-09-19
─ Packages ───────────────────────────────────────────────────────────────────────────────────────────────
package * version date lib source
assertthat 0.2.1 2019-03-21 [3] CRAN (R 3.6.0)
backports 1.1.6 2020-04-05 [3] CRAN (R 3.6.0)
bit 1.1-15.2 2020-02-10 [3] CRAN (R 3.6.0)
bit64 0.9-7 2017-05-08 [3] CRAN (R 3.6.0)
blob 1.2.1 2020-01-20 [3] CRAN (R 3.6.0)
callr 3.4.3 2020-03-28 [3] CRAN (R 3.6.0)
cli 2.0.2 2020-02-28 [3] CRAN (R 3.6.0)
crayon 1.3.4 2017-09-16 [3] CRAN (R 3.6.0)
DBI * 1.1.0 2019-12-15 [3] CRAN (R 3.6.0)
desc 1.2.0 2018-05-01 [3] CRAN (R 3.6.0)
devtools 2.3.0 2020-04-10 [3] CRAN (R 3.6.0)
digest 0.6.25 2020-02-23 [3] CRAN (R 3.6.0)
ellipsis 0.3.0 2019-09-20 [3] CRAN (R 3.6.0)
evaluate 0.14 2019-05-28 [3] CRAN (R 3.6.0)
fansi 0.4.1 2020-01-08 [3] CRAN (R 3.6.0)
fs 1.4.1 2020-04-04 [3] CRAN (R 3.6.0)
glue 1.4.0 2020-04-03 [3] CRAN (R 3.6.0)
hms 0.5.3 2020-01-08 [3] CRAN (R 3.6.0)
htmltools 0.4.0 2019-10-04 [3] CRAN (R 3.6.0)
knitr 1.28 2020-02-06 [3] CRAN (R 3.6.0)
magrittr 1.5 2014-11-22 [3] CRAN (R 3.6.0)
memoise 1.1.0 2017-04-21 [3] CRAN (R 3.6.0)
odbc * 1.2.2 2020-01-10 [3] CRAN (R 3.6.0)
pkgbuild 1.0.6 2019-10-09 [3] CRAN (R 3.6.0)
pkgconfig 2.0.3 2019-09-22 [3] CRAN (R 3.6.0)
pkgload 1.0.2 2018-10-29 [3] CRAN (R 3.6.0)
prettyunits 1.1.1 2020-01-24 [3] CRAN (R 3.6.0)
processx 3.4.2 2020-02-09 [3] CRAN (R 3.6.0)
ps 1.3.2 2020-02-13 [3] CRAN (R 3.6.0)
R6 2.4.1 2019-11-12 [3] CRAN (R 3.6.0)
Rcpp 1.0.4.6 2020-04-09 [3] CRAN (R 3.6.0)
remotes 2.1.1 2020-02-15 [3] CRAN (R 3.6.0)
rlang 0.4.5 2020-03-01 [3] CRAN (R 3.6.0)
rmarkdown 2.1 2020-01-20 [3] CRAN (R 3.6.0)
ROracle * 1.3-1 2016-10-26 [3] CRAN (R 3.6.0)
rprojroot 1.3-2 2018-01-03 [3] CRAN (R 3.6.0)
rstudioapi 0.11 2020-02-07 [3] CRAN (R 3.6.0)
sessioninfo 1.1.1 2018-11-05 [3] CRAN (R 3.6.0)
testthat 2.3.2 2020-03-02 [3] CRAN (R 3.6.0)
usethis 1.6.0 2020-04-09 [3] CRAN (R 3.6.0)
vctrs 0.2.4 2020-03-10 [3] CRAN (R 3.6.0)
withr 2.1.2 2018-03-15 [3] CRAN (R 3.6.0)
xfun 0.13 2020-04-13 [3] CRAN (R 3.6.0)
yaml 2.2.1 2020-02-01 [3] CRAN (R 3.6.0
Reopening since this does still appear to be an issue. Unlike in SQL Server, Oracle can support up to 9 decimals for fractional seconds in the TIMESTAMP data type. R can support up to 6 decimals, so it should be possible to insert a timestamp with 6 decimals and have it match when roundtripped. However, the odbc package appears to be truncating fractional seconds at 3 decimals.
options(digits.secs = 6)
df <- data.frame(DATETIME = Sys.time(), stringsAsFactors = FALSE)
library(odbc)
# the time has 6 decimals in R
print(df)
# DATETIME
# 1 2020-10-23 13:38:10.525016
# odbc connection
con <- DBI::dbConnect(odbc::odbc(),
driver = "Oracle18",
uid = "username",
pwd = "password",
dbq = "database",
timezone = Sys.timezone(),
timezone_out = Sys.timezone())
DBI::dbExecute(con, "create table test_table (datetime timestamp(6))")
# insert data
DBI::dbExecute(con, "insert into test_table (datetime) values (?)", params = df)
# the time only has 3 decimals when read back from Oracle
DBI::dbGetQuery(con, "select * from test_table")
# DATETIME
# 1 2020-10-23 13:38:10.525
DBI::dbExecute(con, "drop table test_table")
Yes this truncation is required by SQLServer limitations, it is unlikely to change. If you want these types of vendor specific behaviors I would suggest you use the vendor specific native driver, e.g. ROracle. Native drivers if written well should basically always have better performance and adhere more closely to the capabilities of the given database then the more generic ODBC driver.
