[R] RODBC and Date/Time variables
ggrothendieck at gmail.com
Mon May 28 15:40:43 CEST 2007
Without any code in your post (see last line of every post to r-help)
its impossible to really know what you are doing but here is an
channel <- odbcConnect("db01") # existing data base
# add a table with a datetime field
sqlQuery(channel, "create table table02 (field01 datetime, field02 integer)")
# insert a row of data into it
'insert into table02 (field01, field02) values ("2002-12-22 12:35:44", 10)')
# look at the data
sqlQuery(channel, "select * from table02")
# the datetime field is POSIXct in R
On 5/28/07, David Scott <d.scott at auckland.ac.nz> wrote:
> This is really a query about MySQL which I am trying to use to set up a
> database which I will then access with RODBC.
> I have my data in a .csv file, and some of the fields are date/time
> fields. I tried to create a table using mysql with the definition of the
> date/time field given by
> CallDate DATETIME,
> but I got an error saying that the first row has
> 28/07/2006 0:00
> in that field. What I don't understand is if I open up the .csv file
> using XEmacs and look at that datum I see
> 2006-07-28 00:00:00
> which mysql should accept as a DATETIME value.
> If I specify that the field is text with
> CallDate CHAR(19),
> then I can create the table without a problem. Then when I issue a query
> and obtain the value of the field in the first row RODBC gives me
> 28/07/2006 0:00
> which I guess doesn't surprise me.
> Can anybody offer an explanation, and also can anybody advise how to read
> the field in as a DATETIME type in MySQL?
> I have some other date fields too which in XEmacs show as for example
> My understanding is I can't read those into a MySQL table as type DATE.
> Can anyone offer some advice on that?
> Some details:
> Windows XP, R 2.4.1, RODBC 1.1-9, MySQL 5.0.41
> Thanks in advance
> David Scott
> David Scott Department of Statistics, Tamaki Campus
> The University of Auckland, PB 92019
> Auckland 1142, NEW ZEALAND
> Phone: +64 9 373 7599 ext 86830 Fax: +64 9 373 7000
> Email: d.scott at auckland.ac.nz
> Graduate Officer, Department of Statistics
> Director of Consulting, Department of Statistics
> R-help at stat.math.ethz.ch mailing list
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
More information about the R-help