POSIX binding drops TZ #191
Description
The TZ is preserved with dbWriteTable but not with a query.
library(DBI) con <- dbConnect(RPostgres::Postgres(), host="...", user="...", password="...") dbExecute(con, "drop table if exists quux") # [1] 0 dbExecute(con, "create table quux (id INT, dt TIMESTAMPTZ)") # [1] 0 ( now <- Sys.time() ) # [1] "2018-07-24 21:55:11 PDT" dbExecute(con, sprintf("insert into quux (id,dt) values (1,'%s')", withr::with_options(list(digits.secs=6), format(now, usetz=TRUE)))) # [1] 1 res <- dbSendStatement(con, "insert into quux (id,dt) values ($1,$2)") dbBind(res, list(2L, now)) dbGetRowsAffected(res) # [1] 1 dbClearResult(res) dbWriteTable(con, "quux", data.frame(id=3L, dt=now), append=TRUE) dbGetQuery(con, "select * from quux") # id dt # 1 1 2018-07-24 21:55:11 # 2 2 2018-07-24 14:55:11 # 3 3 2018-07-24 21:55:11 dbGetQuery(con, "select cast(dt as varchar(32)) as dtchr from quux") # dtchr # 1 2018-07-25 04:55:11.522752+00 # 2 2018-07-24 21:55:11+00 # 3 2018-07-25 04:55:11.522752+00(I'm currently in PDT aka -07.) The first (direct, no binding) and third (dbInsertTable) seem to work, but the parameter is being converted with as.character instead of perhaps format_keep_na.
In this instance, win10, R-3.3, RPostgres-1.1.1, DBI-1.0.0. I'll test in R-3.4 eventually, but I thought the use of as.character to be version-ambivalent.