Re: [R] RODBC and Date/Time variables

From: Prof Brian Ripley <ripley_at_stats.ox.ac.uk>
Date: Mon, 28 May 2007 13:56:25 +0100 (BST)

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

______________________________________________
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.
Received on Mon 28 May 2007 - 13:01:10 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 28 May 2007 - 13:31:16 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.