`CURRENT_TIMESTAMP` returns offset in time despite specifying `timezone =` in `dbConnect()` with Oracle

[ThomasSoeiro]

Using Oracle, CURRENT_TIMESTAMP returns a date-time with an offset in time despite specifying timezone = in dbConnect():

library(DBI)

Sys.setenv(TZ = "Europe/Paris")
Sys.time()
# [1] "2023-12-14 18:01:37 CET"

query <- "SELECT CURRENT_TIMESTAMP AS DTE FROM DUAL;"

conn <- dbConnect(odbc::odbc(),
                  dsn = "xxx",
                  timezone = "Europe/Paris",
                  timezone_out = "Europe/Paris")
dbGetQuery(conn, query)$DTE
# [1] "2023-12-14 17:01:40 CET"
dbDisconnect(conn)

ORA_SDTZ must be set to get the correct result:

Sys.setenv(ORA_SDTZ = "Europe/Paris")

conn <- dbConnect(odbc::odbc(),
                  dsn = "xxx",
                  timezone = "Europe/Paris",
                  timezone_out = "Europe/Paris")
dbGetQuery(conn, query)$DTE
# [1] "2023-12-14 18:01:46 CET"
dbDisconnect(conn)

OTOH, specifying timezone = in dbConnect() is sufficient to get correct date-times using TIMESTAMP:

query <- "SELECT TIMESTAMP '2020-01-01 12:00:00' AS DTE FROM DUAL;"

Sys.setenv(ORA_SDTZ = "")
conn <- dbConnect(odbc::odbc(),
                  dsn = "xxx",
                  timezone = "Europe/Paris",
                  timezone_out = "Europe/Paris")
dbGetQuery(conn, query)$DTE
# [1] "2020-01-01 12:00:00 CET"
dbDisconnect(conn)

Database

```r dbGetInfo(conn) # $dbname # [1] "" # # $dbms.name # [1] "Oracle" # # $db.version # [1] "19.00.0000" # # $username # [1] "" # # $host # [1] "" # # $port # [1] "" # # $sourcename # [1] "xxx" # # $servername # [1] "xxx" # # $drivername # [1] "RStudio Oracle ODBC Driver" # # $odbc.version # [1] "03.52" # # $driver.version # [1] "2.0.2.1002" # # $odbcdriver.version # [1] "03.80" # # $supports.transactions # [1] TRUE # # $getdata.extensions.any_column # [1] TRUE # # $getdata.extensions.any_order # [1] TRUE # # attr(,"class") # [1] "Oracle" "driver_info" "list" ```

Session Info

```r # R version 4.1.2 (2021-11-01) # Platform: x86_64-pc-linux-gnu (64-bit) # Running under: Red Hat Enterprise Linux Server 7.8 (Maipo) # # Matrix products: default # BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so # # locale: # [1] LC_CTYPE=fr_FR.UTF-8 LC_NUMERIC=C LC_TIME=fr_FR.UTF-8 LC_COLLATE=fr_FR.UTF-8 # [5] LC_MONETARY=fr_FR.UTF-8 LC_MESSAGES=fr_FR.UTF-8 LC_PAPER=fr_FR.UTF-8 LC_NAME=C # [9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C # # attached base packages: # [1] stats graphics grDevices datasets utils methods base # # other attached packages: # [1] DBI_1.0.0 # # loaded via a namespace (and not attached): # [1] bit_1.1-14 odbc_1.3.5 compiler_4.1.2 ellipsis_0.3.2 hms_1.1.1 tools_4.1.2 Rcpp_1.0.7 bit64_0.9-7 # [9] vctrs_0.3.8 blob_1.2.2 lifecycle_1.0.1 pkgconfig_2.0.2 rlang_0.4.12 ```

[ThomasSoeiro]

Umh, this seems to work as documented...

From Oracle's documentation:

Datetime Function	Description
CURRENT_DATE	Returns the current date in the session time zone in a value in the Gregorian calendar, of the DATE datatype
SYSDATE	Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.

And from ?dbConnect, timezone = sets "The Server time zone."

Same reprex than above but with SYSDATE instead of CURRENT_DATE:

library(DBI)

Sys.setenv(TZ = "Europe/Paris")
Sys.time()
# [1] "2023-12-15 11:36:37 CET"

query <- "SELECT SYSDATE AS DTE FROM DUAL;"

conn <- dbConnect(odbc::odbc(),
                  dsn = "xxx",
                  timezone = "Europe/Paris",
                  timezone_out = "Europe/Paris")
dbGetQuery(conn, query)$DTE
# [1] "2023-12-15 11:36:38 CET"
dbDisconnect(conn)

Sys.setenv(ORA_SDTZ = "Europe/Paris")
conn <- dbConnect(odbc::odbc(),
                  dsn = "xxx",
                  timezone = "Europe/Paris",
                  timezone_out = "Europe/Paris")
dbGetQuery(conn, query)$DTE
# "2023-12-15 11:36:38 CET"
dbDisconnect(conn)

So finally the question is: should timezone = (or a new argument) also set the session time zone?

[simonpcouch]

Interesting... I can't reproduce that offset in CURRENT_TIMESTAMP despite specifying timezone and timezone_out.

On Linux with Posit Pro Driver, like your setup:

Sys.getenv(c("TZ", "ORA_SDTZ"))
#>       TZ ORA_SDTZ 
#>       ""       ""

library(odbc)

con <- dbConnect(odbc(), dsn = "ProOracle",
                 timezone = "America/Chicago", timezone_out = "America/Chicago")

Sys.time()
#> [1] "2024-07-17 15:51:28 CDT"

query <- "SELECT CURRENT_TIMESTAMP AS DTE FROM DUAL;"

dbGetQuery(con, query)$DTE
#> [1] "2024-07-17 15:51:28 CDT"

dbGetInfo(con)

``` dbGetInfo(con) #> $dbname #> [1] "" #> #> $dbms.name #> [1] "Oracle" #> #> $db.version #> [1] "21.00.0000" #> #> $username #> [1] "RodbcR" #> #> $host #> [1] "" #> #> $port #> [1] "" #> #> $sourcename #> [1] "ProOracle" #> #> $servername #> [1] "FREEPDB1" #> #> $drivername #> [1] "RStudio Oracle ODBC Driver" #> #> $odbc.version #> [1] "03.52" #> #> $driver.version #> [1] "2.0.7.1009" #> #> $odbcdriver.version #> [1] "03.80" #> #> $supports.transactions #> [1] TRUE #> #> $supports.catalogs #> [1] FALSE #> #> $supports.schema #> [1] TRUE #> #> $getdata.extensions.any_column #> [1] TRUE #> #> $getdata.extensions.any_order #> [1] TRUE ```

On macOS with Oracle's driver:

Sys.getenv(c("TZ", "ORA_SDTZ"))
#>       TZ ORA_SDTZ 
#>       ""       ""

library(odbc)
con <- dbConnect(odbc(), dsn = "Oracle", `Service Name` = "orclpdb1", 
                  DBQ = "db", UID = "test", PWD = "odbc",
                 timezone = "America/Chicago", timezone_out = "America/Chicago")

Sys.time()
#> [1] "2024-07-17 15:53:18 CDT"

query <- "SELECT CURRENT_TIMESTAMP AS DTE FROM DUAL;"

dbGetQuery(con, query)$DTE
#> [1] "2024-07-17 15:53:18 CDT"

dbGetInfo(con)

``` dbGetInfo(con) #> $dbname #> [1] "" #> #> $dbms.name #> [1] "Oracle" #> #> $db.version #> [1] "19.00.0000" #> #> $username #> [1] "test" #> #> $host #> [1] "" #> #> $port #> [1] "" #> #> $sourcename #> [1] "Oracle" #> #> $servername #> [1] "db" #> #> $drivername #> [1] "SQORA32.DLL" #> #> $odbc.version #> [1] "03.52" #> #> $driver.version #> [1] "23.03.00239" #> #> $odbcdriver.version #> [1] "03.52" #> #> $supports.transactions #> [1] TRUE #> #> $supports.catalogs #> [1] FALSE #> #> $supports.schema #> [1] TRUE #> #> $getdata.extensions.any_column #> [1] TRUE #> #> $getdata.extensions.any_order #> [1] TRUE ```

Setting Sys.setenv(TZ = "America/Chicago") and/or ORA_SDTZ doesn't affect results on either machine.

There have been a few Pro Driver releases since 2.0.2.1002. Could you install an updated driver and confirm that that doesn't address the issue?

[ThomasSoeiro]

There have been a few Pro Driver releases since 2.0.2.1002. Could you install an updated driver and confirm that that doesn't address the issue?

I don't have the rights to install anything myself on our R server. My IT department has been planning to upgrade the drivers "soon" since last year... I'll test and report back when they do.

FWIW, with an (outdated) Oracle driver on Linux I get:

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "zzz")
dbGetQuery(con, "select current_timestamp as dte from dual")
# Erreur : nanodbc/nanodbc.cpp:1769: 00000: [Oracle][ODBC][Ora]Optional feature not implemented.                                                     
# <SQL> 'select current_timestamp as dte from dual'
dbDisconnect(con)

Database

```r dbGetInfo(conn) # $dbname # [1] "" # # $dbms.name # [1] "Oracle" # # $db.version # [1] "19.00.0000" # # $username # [1] "" # # $host # [1] "" # # $port # [1] "" # # $sourcename # [1] "zzz" # # $servername # [1] "zzz" # # $drivername # [1] "SQORA32.DLL" # # $odbc.version # [1] "03.52" # # $driver.version # [1] "12.01.0020" # # $odbcdriver.version # [1] "03.52" # # $supports.transactions # [1] TRUE # # $getdata.extensions.any_column # [1] TRUE # # $getdata.extensions.any_order # [1] TRUE # # attr(,"class") # [1] "Oracle" "driver_info" "list" ```

[simonpcouch]

Haha, heard! Thanks for the quick response @ThomasSoeiro, we'll leave this open a bit longer in case you're able to see if a newer Pro Driver resolves the issue.