[R] RMySQL and dates

[BrettGinsburg]

I am trying to read and write database tables that have a date field in 
them.

I am constructing a data.frame, then using dbWriteTable to create the 
table and dbReadTable to read it.

 >datatbl<-data.frame( dates, trialnum, fooddel, ethdel, trialtime, 
trialtype, deliveries, food, ethanol, fcumrec, dcumrec, rrf, rrd)

 >ifelse(startdate==filelist, filetbl<-datatbl,filetbl<-rbind(filetbl, 
datatbl))

 >dbWriteTable(con, subj, as.data.frame(filetbl), overwrite=T)

This creates the table named for the contents of subj...the problem is 
in the class of the first column, dates.

 > sapply(filetbl, class)
      dates   trialnum    fooddel     ethdel  trialtime  trialtype 
deliveries
     "Date"  "integer"  "numeric"  "numeric"  "numeric"  "numeric" 
"numeric"
       food    ethanol    fcumrec    dcumrec        rrf        rrd
  "numeric"  "numeric"  "numeric"  "numeric"  "numeric"  "numeric"
 > dbGetQuery(con, "describe SAC11;")
         Field       Type Null Key Default Extra
1   row_names       text  YES        <NA>
2       dates       text  YES        <NA>
3    trialnum bigint(20)  YES        <NA>
4     fooddel     double  YES        <NA>
5      ethdel     double  YES        <NA>
6   trialtime     double  YES        <NA>
7   trialtype     double  YES        <NA>
8  deliveries     double  YES        <NA>
9        food     double  YES        <NA>
10    ethanol     double  YES        <NA>
11    fcumrec     double  YES        <NA>
12    dcumrec     double  YES        <NA>
13        rrf     double  YES        <NA>
14        rrd     double  YES        <NA>
 >

So you can see that the type is change to text.
Now, I read the table into a data.frame named for the content of object 
subj:

 > assign(subj,dbReadTable(con, subj))
 > sapply(SAC11, class)
       dates    trialnum     fooddel      ethdel   trialtime   trialtype
"character"   "numeric"   "numeric"   "numeric"   "numeric"   "numeric"
  deliveries        food     ethanol     fcumrec     dcumrec         rrf
   "numeric"   "numeric"   "numeric"   "numeric"   "numeric"   "numeric"
         rrd
   "numeric"
 >
Still a character class.....What I CAN do is this:

 > SAC11$dates<-as.Date(SAC11$dates)
 > sapply(SAC11, class)
      dates   trialnum    fooddel     ethdel  trialtime  trialtype 
deliveries
     "Date"  "numeric"  "numeric"  "numeric"  "numeric"  "numeric" 
"numeric"
       food    ethanol    fcumrec    dcumrec        rrf        rrd
  "numeric"  "numeric"  "numeric"  "numeric"  "numeric"  "numeric"
 >

BUT, because this is imbedded in a loop in a script where
 > for (i in c(2,3,4,5,6,10,11)
    {
	subj<-paste("SAC", i)
          .....
     }

where i is each subj unique id, I don't know how to do the as.Date 
command with the contents of subj.

Please let me know how I can help to clarify my problem or if I should 
provide additional info...

Thanks!
Brett


-- 
Dr. Brett Ginsburg
Assistant Professor
Department of Psychiatry
The University of Texas Health Science Center at San Antonio
San Antonio, TX 78229
 >
210-567-0871 (p)
210-567-5381 (f)
 >