[R] accessing dates with ROracle

lb59@rz.uni-karlsruhe.de lb59 at rz.uni-karlsruhe.de
Sat Jul 27 13:27:09 CEST 2002


I have a problem with the DBI / ROracle interface.
Most things work very well, but when I try to access
date/time fields, the R string representation only
contains the date part, not the time.

More exactly:
- the Oracle column type is DATE(7)
- example data stored in this column is
     2001-11-23 07:27:51
- I can see this data correctly with database
   tools as sqlplus and tora
- When I access the data from R with 

ora = dbDriver("Oracle", 25);
con = dbConnect(ora, user="user", password="secret", dbname="ora");
rs = dbExecStatement(con, statement = "select * from example");
dta = fetch(rs, n = 10);

the date/time column reads
(i.e., without the time).

As I noticed that I get the correct string conversion *inside*
oracle, e.g. saying "select id,to_char(dat) from example"
in sqlplus or tora, I tried several ideas for workarounds:
  - changing the query to "select id,to_char(dat) from example",
  - creating a view in oracle using this string conversion.
Unfortunately the "bug" (if any) in R is smart enough to 
circumvent this workarounds, that means, the date/time still
is shortened to the date only part.

I am using:
- R 1.5.0
- DBI 0.1.3
- ROracle 0.3.3
- Oracle 9.2i
on a SuSE 8.0 Linux box.

Any ideas what is going wrong and how I can fix this? 

Thanks a lot in advance, and thanks for ROracle, that works
great so far for anything else!

r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch

More information about the R-help mailing list