dbGet/SendQuery: Dates shifted to day before

[matthijsvanderloos]

Issue Description and Expected Result

When querying date columns from MSSQL dates are shifted to the day before. For example, 2019-01-01 is returned as 2018-12-31 in R. Timestamps are returned OK. When I unclass() the returned date it is fractional.

I am using the timezone argument of dbConnect() as the database timezone is set to CET instead of UTC.

Might be related to #61 and #95?

Database

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)
Jun 15 2019 00:26:19
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

Reproducible Example

library(DBI)
library(odbc)

Sys.timezone()
#> [1] "Europe/Berlin"
  
con <- DBI::dbConnect(odbc::odbc()
                      , driver = "ODBC Driver 13 for SQL Server"
                      , server = <removed>
                      , trusted_connection = "Yes"
                      , timezone = Sys.timezone()
)
res <- DBI::dbSendQuery(con, "SELECT CAST('2019-01-01' AS date) AS casted_date, CAST('2019-01-01' AS datetime) AS casted_datetime")

# Data types seem fine
odbc:::result_column_info(res@ptr)
#>              name type
#> 1     casted_date   91
#> 2 casted_datetime   93

data <- DBI::dbFetch(res)
str(data)
#> 'data.frame':    1 obs. of  2 variables:
#>  $ casted_date    : Date, format: "2018-12-31"
#>  $ casted_datetime: POSIXct, format: "2018-12-31 23:00:00"

# But casted_date is day before!
data$casted_date
#> [1] "2018-12-31"
# And is a fractional date?
unclass(data$casted_date)
#> [1] 17896.96

# Timestamp is fine
data$casted_datetime
#> [1] "2018-12-31 23:00:00 UTC"

^{Created on 2019-08-23 by the reprex package (v0.3.0)}

Session Info

R version 3.5.3 (2019-03-11)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=Dutch_Netherlands.1252  LC_CTYPE=Dutch_Netherlands.1252    LC_MONETARY=Dutch_Netherlands.1252 LC_NUMERIC=C
[5] LC_TIME=Dutch_Netherlands.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] odbc_1.1.6     DBI_1.0.0.9001

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.2      zeallot_0.1.0   crayon_1.3.4    backports_1.1.4 pillar_1.4.2    rlang_0.4.0     rstudioapi_0.10 blob_1.2.0
 [9] vctrs_0.2.0     tools_3.5.3     bit64_0.9-7     bit_1.1-14      hms_0.5.0       compiler_3.5.3  pkgconfig_2.0.2 tibble_2.1.3

[shrektan]

It's about the timezone. odbc will always return date/datetime in UTC.

(I don't have the access to SQL Server now but I remember the date is converted to strings in R so should not have this issue. )

Anyway, using lubridate::date(lubridate::with_tz(data$casted_date, Sys.timezone())) should lead to a desired value.

We are not able to control the returned timezone in odbc for now, which is a bit of inconvinient... Returning UTC based datetime is not what I want usually... I prefer the returned timezone is the same as the server...

[matthijsvanderloos]

Hi shrektan,

I am not sure I understand your answer. My issue concerns the casted_date column which is a date in MSSQL and returned as a Date in R. Hence, it should not have any timezone attached to it. Unfortunately, your solution returns 2018-12-31 instead of 2019-01-01.

[shrektan]

(I know why the workaround won't work - odbc has converted to date already...)

First of all, on my computer, the date in MSSQL (version 2014) returns a character in R (what I get from odbc:::result_column_info(res@ptr) is type -9 for name casted_date). So I cannot reproduce your issue for now.

However, if it returns a Date in R it means (not sure but highly probably) :

Internally, odbc receives a datetime object first, then mark it as UTC, then converts the datetime object to your timezone, then the date part is extracted from that. Since the datetime object gets converted (from UTC to your timezone), extracting the date part may lead to one-day off from the real value.

For example, '2019-01-01' (MSSQL no timezone) -> 2019-01-01 00:00:00 CET (nanodbc time object tz is marked as Europe/Berlin) -> convert to UTC 2018-12-31 23:00:00 UTC(odbc) -> extract the date part (2018-12-31) -> returned to R.

odbc/src/odbc_result.cpp

Line 419 in 24bf73b

auto sec = convert(civil_day(dt.year, dt.month, dt.day), c_->timezone());

So I bet you will get a correct result for date if you provide "UTC" timezone in the connection.

con <- DBI::dbConnect(odbc::odbc()
                      , driver = "ODBC Driver 13 for SQL Server"
                      , server = <removed>
                      , trusted_connection = "Yes"
                      , timezone = "UTC"
)

[shrektan]

@matthijsvanderloos Would you mind test my try-to-fix-this-issue branch? Install it by running:

remotes::install_github('shrektan/odbc', ref = 'fix293')

Please let me know if it works (sorry that I can't test it in advance because odbc somehow doesn't recongize date as Date on my computer).

(Please use timezone = Sys.timezone() as in your original example)

Thanks!

[shrektan]

@matthijsvanderloos I just confirmed my patch works (Using driver = '{SQL Server Native Client 11.0}' and now I have can get the date returned directly from odbc). So I will file a PR.

[matthijsvanderloos]

@shrektan I can confirm that your patch also works on my setup. Thanks!