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

ThomasSoeiro · 2023-12-14T17:17:09Z

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

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 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

The text was updated successfully, but these errors were encountered:

ThomasSoeiro · 2023-12-15T08:47:51Z

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 · 2024-07-17T21:07:10Z

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 · 2024-07-18T12:50:10Z

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

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 · 2024-07-18T12:55:06Z

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.

hadley added the oracle label Apr 4, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

ThomasSoeiro commented Dec 14, 2023 •

edited

Loading

ThomasSoeiro commented Dec 15, 2023 •

edited

Loading

simonpcouch commented Jul 17, 2024

ThomasSoeiro commented Jul 18, 2024 •

edited

Loading

simonpcouch commented Jul 18, 2024

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

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

Comments

ThomasSoeiro commented Dec 14, 2023 • edited Loading

ThomasSoeiro commented Dec 15, 2023 • edited Loading

simonpcouch commented Jul 17, 2024

ThomasSoeiro commented Jul 18, 2024 • edited Loading

simonpcouch commented Jul 18, 2024

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

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

ThomasSoeiro commented Dec 14, 2023 •

edited

Loading

ThomasSoeiro commented Dec 15, 2023 •

edited

Loading

ThomasSoeiro commented Jul 18, 2024 •

edited

Loading