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 18/01/2007 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
I do this by having a POSIXct variable in R and using sqlSave in RODBC to write the SQL table. Examples are part of my test suite, so I am sure they do work. It is not at all clear how you are trying to import the data into MySQL. On Tue, 29 May 2007, David Scott 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 > 18/01/2007 > > 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 > 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. >-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
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 example: library(RODBC) 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 sqlQuery(channel, '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 str(.Last.value) odbcClose(channel) 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 > 18/01/2007 > > 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 > 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. >