odbc
odbc copied to clipboard
r-dbi
Timestamp column with time zone for PostgreSQL
I found that odbc package creates timestamp columns without time zone, even if the time zone is present in R. This can shift time when reading and writing in different time zones. I observed this on Postgres 10.0.3 with odbc 1.1.5 (r-dbi/odbc@492f539).
Example (my local time zone is EDT):
library(DBI)
con <- dbConnect(odbc::odbc(), "PostgreSQL")
(x <- data.frame(time = as.POSIXlt(Sys.time())))
#> time
#> 1 2018-04-08 20:53:25
x$time # just to be sure
#> [1] "2018-04-08 20:53:25 EDT"
dbWriteTable(con, "ts", x, overwrite = TRUE)
dbReadTable(con, "ts")
#> time
#> 1 2018-04-09 00:53:25
The time zone was transformed to UTC, but it's not marked in the database. I think it would be better to create a timestamp with time zone column and specify UTC.
Session Info
devtools::session_info()
#> Session info -------------------------------------------------------------
#> setting value
#> version R version 3.4.4 (2018-03-15)
#> system x86_64, linux-gnu
#> ui X11
#> language en_CA:en
#> collate en_CA.UTF-8
#> tz America/Toronto
#> date 2018-04-08
#> Packages -----------------------------------------------------------------
#> package * version date source
#> backports 1.1.2 2017-12-13 cran (@1.1.2)
#> base * 3.4.4 2018-03-16 local
#> bit 1.1-12 2014-04-09 CRAN (R 3.4.2)
#> bit64 0.9-7 2017-05-08 CRAN (R 3.4.2)
#> blob 1.1.1 2018-03-25 cran (@1.1.1)
#> compiler 3.4.4 2018-03-16 local
#> datasets * 3.4.4 2018-03-16 local
#> DBI * 0.8 2018-04-08 Github (jimhester/DBI@17f7e8f)
#> devtools 1.13.5 2018-02-18 CRAN (R 3.4.3)
#> digest 0.6.15 2018-01-28 cran (@0.6.15)
#> evaluate 0.10.1 2017-06-24 CRAN (R 3.4.2)
#> graphics * 3.4.4 2018-03-16 local
#> grDevices * 3.4.4 2018-03-16 local
#> hms 0.4.2 2018-03-10 cran (@0.4.2)
#> htmltools 0.3.6 2017-04-28 CRAN (R 3.4.1)
#> knitr 1.19 2018-01-29 CRAN (R 3.4.3)
#> magrittr 1.5 2014-11-22 CRAN (R 3.4.0)
#> memoise 1.1.0 2017-04-21 CRAN (R 3.4.0)
#> methods * 3.4.4 2018-03-16 local
#> odbc 1.1.5 2018-04-08 Github (r-dbi/odbc@492f539)
#> pkgconfig 2.0.1 2017-03-21 CRAN (R 3.4.2)
#> Rcpp 0.12.16 2018-03-13 cran (@0.12.16)
#> rlang 0.2.0 2018-02-20 cran (@0.2.0)
#> rmarkdown 1.8 2017-11-17 CRAN (R 3.4.3)
#> rprojroot 1.3-2 2018-01-03 cran (@1.3-2)
#> stats * 3.4.4 2018-03-16 local
#> stringi 1.1.7 2018-03-12 cran (@1.1.7)
#> stringr 1.3.0 2018-02-19 cran (@1.3.0)
#> tools 3.4.4 2018-03-16 local
#> utils * 3.4.4 2018-03-16 local
#> withr 2.1.2 2018-03-15 cran (@2.1.2)
#> yaml 2.1.16 2017-12-12 CRAN (R 3.4.3)
dplyr::glimpse(dbGetInfo(con))
#> List of 13
#> $ dbname : chr "etienne"
#> $ dbms.name : chr "PostgreSQL"
#> $ db.version : chr "10.0.3"
#> $ username : chr "etienne"
#> $ host : chr ""
#> $ port : chr ""
#> $ sourcename : chr "PostgreSQL"
#> $ servername : chr "localhost"
#> $ drivername : chr "psqlodbca.so"
#> $ odbc.version : chr "03.52"
#> $ driver.version : chr "10.01.0000"
#> $ odbcdriver.version : chr "03.51"
#> $ supports.transactions: logi TRUE
#> - attr(*, "class")= chr [1:3] "PostgreSQL" "driver_info" "list"
I agree, I am just not positive that timestamp with time zone is portable across all databases.
How difficult would it be to handle time-zones correctly at least for reading? Currently I see that reading from a timestamp with time zone fields ignores the timezone on postrgres.
