[R] RODBC and Date/Time variables

[David Scott]

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: [phone] ext 86830 Fax: [phone]
Email: d.scott at auckland.ac.nz

Graduate Officer, Department of Statistics
Director of Consulting, Department of Statistics

[Prof Brian Ripley]

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.

Post by David Scott
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?
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: [phone] ext 86830 Fax: [phone]
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.

[Gabor Grothendieck]

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)

Post by David Scott
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?
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: [phone] ext 86830 Fax: [phone]
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.