datetimeoffset unsupported

[r2evans]

Issue Description and Expected Result

The SQL Server DATETIMEOFFSET field is not recognized and/or formatted correctly. Instead of a "usable" timezone, it's including a rather-verbose version: "2018-08-16 23:14:18 Pacific Standard Time". I can't find it anywhere in the source for odbc or DBI, though I do find it in the R source extra/tzone/registryTZ.c. I know *this* one, but I can't assume anything.

I'm not 100% certain this is an odbc-thing, frankly, but I am also not where else the problem can be addressed.

Database

1> select @@VERSION
2> GO
Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) 
	Jul  6 2018 18:24:36 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

Reproducible Example

library(DBI)
con <- dbConnect(odbc::odbc(),
                   driver='ODBC Driver 13 for SQL Server',
                   server='127.0.0.1,31433',
                   database='master',
                   uid='SA', pwd='Mysecretpassword1')

qry <- "create table test1 ( id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY NOT NULL , f_int INT , f_psx DATETIME, f_psx2 DATETIMEOFFSET )"
data1 <- data.frame(
    f_int  = 1L,
    f_psx  = Sys.time(),
    f_psx2 = Sys.time(),
    stringsAsFactors=FALSE
  )
withr::with_options(list(digits.secs=6), print(data1$f_psx))
# [1] "2018-08-16 23:14:18.559138 UTC" # currently -7, so 23:14:18 UTC
attr(data1$f_psx, "tz") # to show on TZ assigned yet
# NULL

dbExecute(con, qry)
# [1] 0
dbWriteTable(con, 'test1', data1, create=FALSE, append=TRUE)
str( dbGetQuery(con, "select * from test1") )
# 'data.frame':	1 obs. of  4 variables:
#  $ id    : chr "7DDA423E-F36B-1410-8A8F-007D77BBB095"
#  $ f_int : int 1
#  $ f_psx : POSIXct, format: "2018-08-16 23:14:18"
#  $ f_psx2: chr "2018-08-16 23:14:18 Pacific Standard Time"

Most of the $f_psx2 string is easily parsed into POSIXt, but the TZ is confounding me.

I would be happier with just the character representation from the server, frankly, since that contains a numeric offset and therefore much easier to parse.

For the record:

Sys.timezone()
# [1] "America/Los_Angeles"

This is what the database sees:

1> select f_int,f_psx,f_psx2 from test1
2> go
f_int       f_psx                   f_psx2
----------- ----------------------- ---------------------------------------------
          1 2018-08-16 23:14:18.000            2018-08-16 23:14:18.0000000 -07:00

and similarly, Excel with Microsoft Query:

So the first column is just a DATETIME sans TZ info, which is unacceptable for my use (I could soap-box a long time about databasers who think it's okay to trust clients and programs to always insert times with the same TZ offset).

This doesn't change if R has a TZ attached to the data:

data1$f_int <- 2L
( data1$f_psx <- .POSIXct(data1$f_psx, "US/Pacific") )
# [1] "2018-08-16 16:14:18 PDT"
dbWriteTable(con, 'test1', data1, create=FALSE, append=TRUE)
str( dbGetQuery(con, "select * from test1 where f_int = 2") )
# 'data.frame':	1 obs. of  4 variables:
#  $ id    : chr "80DA423E-F36B-1410-8A8F-007D77BBB095"
#  $ f_int : int 2
#  $ f_psx : POSIXct, format: "2018-08-16 23:14:18"
#  $ f_psx2: chr "2018-08-16 23:14:18 Pacific Standard Time"

data1$f_int <- 3L
( data1$f_psx <- .POSIXct(data1$f_psx, "UTC") )
# [1] "2018-08-16 23:14:18 UTC"
dbWriteTable(con, 'test1', data1, create=FALSE, append=TRUE)
str( dbGetQuery(con, "select * from test1 where f_int = 3") )
# 'data.frame':	1 obs. of  4 variables:
#  $ id    : chr "87DA423E-F36B-1410-8A8F-007D77BBB095"
#  $ f_int : int 3
#  $ f_psx : POSIXct, format: "2018-08-16 23:14:18"
#  $ f_psx2: chr "2018-08-16 23:14:18 Pacific Standard Time"

Session Info

devtools::session_info()
# Session info ------------------------------------------------------------------
#  setting  value                       
#  version  R version 3.5.1 (2018-07-02)
#  system   x86_64, mingw32             
#  ui       RTerm                       
#  language (EN)                        
#  collate  English_United States.1252  
#  tz       America/Los_Angeles         
#  date     2018-08-16                  
# Packages ----------------------------------------------------------------------
#  package    * version    date       source        
#  assertthat   0.2.0      2017-04-11 CRAN (R 3.5.1)
#  backports    1.1.2      2017-12-13 CRAN (R 3.5.0)
#  base       * 3.5.1      2018-07-02 local         
#  bindr        0.1.1      2018-03-13 CRAN (R 3.5.1)
#  bindrcpp   * 0.2.2      2018-03-29 CRAN (R 3.5.1)
#  bit          1.1-14     2018-05-29 CRAN (R 3.5.0)
#  bit64        0.9-7      2017-05-08 CRAN (R 3.5.0)
#  blob         1.1.1      2018-03-25 CRAN (R 3.5.1)
#  cli          1.0.0      2017-11-05 CRAN (R 3.5.1)
#  colorspace   1.3-2      2016-12-14 CRAN (R 3.5.1)
#  commonmark   1.5        2018-04-28 CRAN (R 3.5.1)
#  compiler     3.5.1      2018-07-02 local         
#  config       0.3        2018-03-27 CRAN (R 3.5.1)
#  crayon       1.3.4      2017-09-16 CRAN (R 3.5.1)
#  datasets   * 3.5.1      2018-07-02 local         
#  DBI        * 1.0.0      2018-05-02 CRAN (R 3.5.1)
#  devtools     1.13.6     2018-06-27 CRAN (R 3.5.1)
#  digest       0.6.15     2018-01-28 CRAN (R 3.5.1)
#  dplyr      * 0.7.6      2018-06-29 CRAN (R 3.5.1)
#  evaluate     0.11       2018-07-17 CRAN (R 3.5.1)
#  fansi        0.2.3      2018-05-06 CRAN (R 3.5.1)
#  ggplot2    * 3.0.0      2018-07-03 CRAN (R 3.5.1)
#  glue         1.3.0      2018-07-17 CRAN (R 3.5.1)
#  graphics   * 3.5.1      2018-07-02 local         
#  grDevices  * 3.5.1      2018-07-02 local         
#  grid         3.5.1      2018-07-02 local         
#  gtable       0.2.0      2016-02-26 CRAN (R 3.5.1)
#  hms          0.4.2      2018-03-10 CRAN (R 3.5.1)
#  htmltools    0.3.6      2017-04-28 CRAN (R 3.5.1)
#  knitr        1.20       2018-02-20 CRAN (R 3.5.1)
#  labeling     0.3        2014-08-23 CRAN (R 3.5.0)
#  lazyeval     0.2.1      2017-10-29 CRAN (R 3.5.1)
#  magrittr     1.5        2014-11-22 CRAN (R 3.5.1)
#  memoise      1.1.0      2017-04-21 CRAN (R 3.5.1)
#  methods    * 3.5.1      2018-07-02 local         
#  munsell      0.5.0      2018-06-12 CRAN (R 3.5.1)
#  odbc         1.1.6      2018-06-09 CRAN (R 3.5.1)
#  pillar       1.3.0      2018-07-14 CRAN (R 3.5.1)
#  pkgconfig    2.0.1      2017-03-21 CRAN (R 3.5.1)
#  plyr         1.8.4      2016-06-08 CRAN (R 3.5.1)
#  purrr      * 0.2.5      2018-05-29 CRAN (R 3.5.1)
#  R6           2.2.2      2017-06-17 CRAN (R 3.5.1)
#  Rcpp         0.12.18    2018-07-23 CRAN (R 3.5.1)
#  rlang        0.2.1      2018-05-30 CRAN (R 3.5.1)
#  rmarkdown    1.10       2018-06-11 CRAN (R 3.5.1)
#  roxygen2     6.1.0      2018-07-27 CRAN (R 3.5.1)
#  rprojroot    1.3-2      2018-01-03 CRAN (R 3.5.1)
#  scales       1.0.0      2018-08-09 CRAN (R 3.5.1)
#  stats      * 3.5.1      2018-07-02 local         
#  stringi      1.2.4      2018-07-20 CRAN (R 3.5.1)
#  stringr      1.3.1      2018-05-10 CRAN (R 3.5.1)
#  testthat     2.0.0      2017-12-13 CRAN (R 3.5.1)
#  tibble       1.4.2      2018-01-22 CRAN (R 3.5.1)
#  tidyselect   0.2.4      2018-02-26 CRAN (R 3.5.1)
#  tools        3.5.1      2018-07-02 local         
#  utf8         1.1.4      2018-05-24 CRAN (R 3.5.1)
#  utils      * 3.5.1      2018-07-02 local         
#  withr        2.1.2      2018-03-15 CRAN (R 3.5.1)
#  xml2         1.2.0      2018-01-24 CRAN (R 3.5.1)
#  yaml         2.2.0      2018-07-25 CRAN (R 3.5.1)