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)

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