Get correct datetime from Oracle query via ROracle in R

Question

I am using ROracle within R to access an Oracle database.

I noticed that probably since the summer-time change, any DATE (datetime) Oracle column are now converted wrongly (I get an hour LESS in R than in the Oracle DB).

From page 12 of the vignette (that I barely understand) it looks like ROracle and R exchange datetimes as number of seconds since 1-1-1970 UTC, and that R subsequently adjust for the local the timezone.

Here is what I do

    drv <- dbDriver("Oracle");
    con <- dbConnect(drv, username = Login, password = Pwd, dbname = DB, prefetch=TRUE, bulk_read=1e6);

    test.query <- "SELECT * FROM MYTABLE WHERE ( A > to_date('2008-03-03 12:30:00', 'YYYY-MM-DD HH24:MI:SS') AND A < to_date('2008-03-03 12:40:00','YYYY-MM-DD HH24:MI:SS')  AND [other stuff])"

    test <- dbGetQuery(con, test.query);

    head(test[,c("A","B","C")])
    #                    A                   B                   C
    #1 2008-03-03 11:30:38 2008-03-02 23:00:00 2008-03-02 23:00:00 #HERE IT SHOULD BE +1 HOUR
    #2 2008-03-03 11:30:38 2008-03-02 23:00:00 2008-03-02 23:00:00
    #3 2008-03-03 11:30:41 2008-03-02 23:00:00 2008-03-02 23:00:00
    #4 2008-03-03 11:31:25 2008-03-02 23:00:00 2008-03-02 23:00:00
    #5 2008-03-03 11:31:25 2008-03-02 23:00:00 2008-03-02 23:00:00
    #6 2008-03-03 11:31:34 2008-03-02 23:00:00 2008-03-02 23:00:00

    class(test$A)
    [1] "POSIXct" "POSIXt" 
    attributes(test$A)
    $class
    [1] "POSIXct" "POSIXt" 

Here is my sessionInfo()

    sessionInfo()
    R version 2.15.2 (2012-10-26)
    Platform: x86_64-w64-mingw32/x64 (64-bit)

    locale:
    [1] LC_COLLATE=French_France.1252  LC_CTYPE=French_France.1252    LC_MONETARY=French_France.1252
    [4] LC_NUMERIC=C                   LC_TIME=French_France.1252    

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

    other attached packages:
    [1] data.table_1.8.9 ROracle_1.1-7    DBI_0.2-5       

    loaded via a namespace (and not attached):
    [1] tools_2.15.2

Here are info about the Oracle DB I got from the admin

What can I do to fix correctly this problem (not just adding 1hour that I will have to take back at the end of summer)

Skardol

I don't have a solution, but I'm curious about potential answers, since I experience the exact same issue (in R 3.0.0, OS X, and a time zone half way across the world, probably). – Eliseo

your problem was with ROracle ? – Skardol

Yes; the exact same thing, datetimes are an hour off. Connecting to the same db with RODBC does not display the problem. – Eliseo

Ohhh so may be it is a problem with Roracle itself ? – Skardol

That has been my working assumption thus far, yes, but I don't know for sure. – Eliseo

I think it is likely the problem, using another client (and SAS) I do not have the issue either. I contacted the package author/maintainer... – Skardol

Hi, I am having the same issue. Did someone here solved or understood that issue? – Operation

I spoke with some guy at oracle, in the past, without success. Though I am not using this anymore so I droped it. Never got it to a working state. For some reason working with SAS is no issue (but SAS is crapy) – Skardol

This seems to be fixed in ROracle-1.1.12. – Bestrew

Answer 1

As you mentioned this is a timezone conversion which R is doing as per local timezone. For more details you can refer this link

http://www.oralytics.com/2015/05/r-roracle-and-oracle-date-formats_27.html

Try setting these system variables before connecting through ROracle

Sys.setenv(TZ = "GMT")
Sys.setenv(ORA_SDTZ = "GMT")

Ergo

Thank you. This is the correct answer as the timezone conversion issue has not changed on the ROracle side of things. Setting both of these environment variables to 'UTC' solved my issue. – Genevagenevan

This answer also worked for me. Thank you for both the answer and documentation. – Annecorinne

Answer 2

This seems to be fixed in ROracle-1.1.12 released on 2014-11-20. See the documentation on ?dbReadTable for how time zones work.

Bestrew