mssql datetimeoffset wrong on upload

[r2evans]

POSIXt uploaded into DATETIMEOFFSET fields are incorrect.

# con <- dbConnect(...)
tbl_name <- "posixt_test"
(tm_tz1 <- as.POSIXct("2022-04-01 12:00:00", tz = "America/New_York"))
(tm_tz2 <- `attr<-`(tm_tz1, "tzone", "Europe/Stockholm"))
(tm_utc <- `attr<-`(tm_tz1, "tzone", "UTC"))
tm_tz1_chr <- sub("00$", ":00", format(tm_tz1, "%Y-%m-%d %H:%M:%OS6 %z"))
tm_tz2_chr <- sub("00$", ":00", format(tm_tz2, "%Y-%m-%d %H:%M:%OS6 %z"))
tm_utc_chr <- sub("00$", ":00", format(tm_utc, "%Y-%m-%d %H:%M:%OS6 %z"))
c(tm_tz1_chr, tm_tz2_chr, tm_utc_chr)
# [1] "2022-04-01 12:00:00 EDT"
# [1] "2022-04-01 18:00:00 CEST"
# [1] "2022-04-01 16:00:00 UTC"
# [1] "2022-04-01 12:00:00.000000 -04:00"
# [2] "2022-04-01 18:00:00.000000 +02:00"
# [3] "2022-04-01 16:00:00.000000 +00:00"

dbWriteTable(con, tbl_name, data.frame(int = 1L, tm = tm_tz1), field.types = c(int = "INT", tm = "DATETIMEOFFSET"))
dbWriteTable(con, tbl_name, data.frame(int = 2L, tm = tm_tz2), append = TRUE)
dbWriteTable(con, tbl_name, data.frame(int = 3L, tm = tm_utc), append = TRUE)
dbWriteTable(con, tbl_name, data.frame(int = 4:6, tm = c(tm_tz1_chr, tm_tz2_chr, tm_utc_chr)), append = TRUE)

rets <- lapply(1:6, function(int) {
  dbGetQuery(con, paste("select int, tm, cast(tm as varchar(max)) as tm_chr from", tbl_name,
                        "where int=? order by int"), params = list(int))
})
rets
# [[1]]
#   int                  tm                             tm_chr
# 1   1 2022-04-01 20:00:00 2022-04-01 16:00:00.0000000 -04:00
# [[2]]
#   int                  tm                             tm_chr
# 1   2 2022-04-01 20:00:00 2022-04-01 16:00:00.0000000 -04:00
# [[3]]
#   int                  tm                             tm_chr
# 1   3 2022-04-01 20:00:00 2022-04-01 16:00:00.0000000 -04:00
# [[4]]
#   int                  tm                             tm_chr
# 1   4 2022-04-01 16:00:00 2022-04-01 12:00:00.0000000 -04:00
# [[5]]
#   int                  tm                             tm_chr
# 1   5 2022-04-01 16:00:00 2022-04-01 18:00:00.0000000 +02:00
# [[6]]
#   int                  tm                             tm_chr
# 1   6 2022-04-01 16:00:00 2022-04-01 16:00:00.0000000 +00:00

sapply(rets, function(ret) difftime(ret$tm, tm_tz1, units = "hours"))
# [1] 4 4 4 0 0 0
### first three are POSIXt uploads, last three are known-correct formatted strings,
### all six should be zero

dbExecute(con, "drop table posixt_test")

While sqlserver does support timezones on input (as shown with tz_*_chr), it seems as if the POSIXt object is converted to UTC (%H:%M:%S) but the TZ offset of the original tzone is appended to the string. Options: (1) retain the original time (hours) and tzoffset; or (2) convert to UTC and always append +00:00.

[detule]

Thanks @r2evans

I am trying to slot this issue into some historical context - please correct me if I am wrong:

• In Add support for SQL Server DATETIMEOFFSET data type #918 and mssql: DATETIMEOFFSET: When transforming to UTC, use ISO 8601 convention #951 we made it possible to retrieve data stored as DATETIMEOFFSET as POSIXct, correctly labeled/shifted to account for the time-zone offset (previously, user had to retrieve as string and parse out the offset manually, I think).
• You are pointing out that when writing POSIXct data into DATETIMEOFFSET targets, the timezone offset is not accounted for correctly.

Thank for the report, I'll take a look.

[r2evans]

I was rather excited with 1.6.4.1 for native datetimeoffset, not (yet) considering the round-trip concerns. Fortunately b/c most of my wrapping code was written years before 1.6.4.1, I already have converters between sqlserver and R, so I did not immediately notice this (forgetting I was converting it myself) until I started writing #984 and some of the round-trip tests failed for unrelated (to that issue) problems.

Thanks. If it were as easy as an R tweak I'd probably work on the PR, but for efficiency this needs to stay in C++ and therefore a little slower for me.

[r2evans]

I will add that my code to demonstrate it above is far beyond what would be necessary to test it, much of that was to exhaustively confirm pos, neg, utc, and non-R behavior. Hope it's not too verbose.

[detule]

Hey @r2evans - thanks again for the report and for championing this feature.

It ended up giving me the runaround, but I think i finally understand what's going on. Do you mind trying this branch? The code there is of prototype quality but I think the datetimeoffset writes should work.

tm_tz1 <- as.POSIXct("2022-04-01 12:00:00", tz = "America/New_York")
dbWriteTable(conn, "posix_test2", data.frame(tm = tm_tz1), field.types = c(tm = "DATETIMEOFFSET"), overwrite = TRUE)
DBI::dbGetQuery(conn, "SELECT tm, CAST(tm AS VARCHAR(MAX)) AS tm_char FROM posix_test2")
                   tm                            tm_char
1 2022-04-01 17:00:00 2022-04-01 12:00:00.0000000 -05:00

[r2evans]

Thank you for working on it!

Something is not right with times with a non-UTC timezone, both of them are an hour off. Using the code at the top of the issue,

rets
# [[1]]
#   int                  tm                             tm_chr
# 1   1 2022-04-01 17:00:00 2022-04-01 12:00:00.0000000 -05:00
# [[2]]
#   int                  tm                             tm_chr
# 1   2 2022-04-01 17:00:00 2022-04-01 18:00:00.0000000 +01:00
# [[3]]
#   int                  tm                             tm_chr
# 1   3 2022-04-01 16:00:00 2022-04-01 16:00:00.0000000 +00:00
# [[4]]
#   int                  tm                             tm_chr
# 1   4 2022-04-01 16:00:00 2022-04-01 12:00:00.0000000 -04:00
# [[5]]
#   int                  tm                             tm_chr
# 1   5 2022-04-01 16:00:00 2022-04-01 18:00:00.0000000 +02:00
# [[6]]
#   int                  tm                             tm_chr
# 1   6 2022-04-01 16:00:00 2022-04-01 16:00:00.0000000 +00:00

sapply(rets, function(ret) difftime(ret$tm, tm_tz1, units = "hours"))
# [1] 1 1 0 0 0 0

Looking at rets:

• all tm should be the same time, the first two are different (last 4 are correct values)
• all tm_chr should "math" (e.g., 12:00 minus -05) to 16, but 12 - -5 and 18 - +1 do not; the fact that they are wrong as strings coming back says that the values stored in the database are wrong, not that they are converted wrong on download;
• the expectation is that the last sapply() would return all 0

As a further side-by-side (since the original code is likely overly-complex), this matrix should have the same value across each row, where the first column is what was stored when inserted as POSIXt, the second column is what was stored when inserted as strings, and the third is what I expect it should be stored as (trailing zeroes notwithstanding).

cbind(sapply(rets[1:3], `[[`, "tm_chr"), sapply(rets[4:6], `[[`, "tm_chr"), sub("0 ", "00 ", c(tm_tz1_chr, tm_tz2_chr, tm_utc_chr)))
#      [,1]                                 [,2]                                 [,3]                                
# [1,] "2022-04-01 12:00:00.0000000 -05:00" "2022-04-01 12:00:00.0000000 -04:00" "2022-04-01 12:00:00.0000000 -04:00"
# [2,] "2022-04-01 18:00:00.0000000 +01:00" "2022-04-01 18:00:00.0000000 +02:00" "2022-04-01 18:00:00.0000000 +02:00"
# [3,] "2022-04-01 16:00:00.0000000 +00:00" "2022-04-01 16:00:00.0000000 +00:00" "2022-04-01 16:00:00.0000000 +00:00"

I haven't looked at your changes (and do not think I will be able to today).

[detule]

Hey I noticed that but I attributed it to daylight savings. I should know this, but isn't the winter-time offset of America/New_York to UTC -5?
Oh crap I know what's going on. In an effort to be expedient in reading the offset, I used the system clock to transform between utc and the timezone. But I can't use any one fixed time because of issues related to daylight savings. So that means I need to look up the offset for each value.

I need to think about how to balance performance with accuracy here.

[r2evans]

I don't think using the system-clock for anything is prudent here, for reasons you identified. I'd think you could use something as simple as as.POSIXlt(val[1])$gmtoff as seconds; this is faster than format(val[1], "%z"), especially since format() requires you to parse the string as well. Both of these return nothing when length(val) == 0, but that isn't a problem.

All of this "cheats" a little since R requires its POSIXt vectors to share the same attributes (tzone).

[detule]

Sweet --- I think i have a good way forward, without introducing any more computation than what we were already doing. I updated the branch with code that produces this:

 ┃  > dbWriteTable(conn, "posix_test2", data.frame(tm = tm_tz1), field.types = c(tm = "DATETIMEOFFSET"), overwrite = TRUE)
 ┃  > DBI::dbGetQuery(conn, "SELECT tm, CAST(tm AS VARCHAR(MAX)) AS tm_char FROM posix_test2")
 ┃                     tm                            tm_char
 ┃  1 2022-04-01 16:00:00 2022-04-01 12:00:00.0000000 -04:00

I'll clean it up and put up a PR. In the meantime feel free to take it for a spin.

Thanks again for bringing this up and the feedback!

[r2evans]

That seems to resolve the code above, thanks for finding a good path!