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)
>
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
