1

I have a time in R which I would like to write to a MySQL table in a DATETIME column. How can I do that? If I format it as a string SQL doesn't recognize it; if I use as.POSIXlt SQL still doesn't recognize it.

I'm just using standard dbConnect and dbWriteTable statements, which work with non-dates just fine.

Charles
  • 11,269
  • 13
  • 67
  • 105
  • Time variables are imported/exported as character data, so you need to convert these to your favorite date/time representation. – agstudy Feb 26 '13 at 02:23
  • @agstudy: It's easy enough to convert character data to dates in R. But how do I update a SQL `DATETIME` field from R? Is there a binary format I can use to transfer between the two? – Charles Feb 26 '13 at 02:42

2 Answers2

6

According to this:

MySQL recognizes DATETIME and TIMESTAMP values in these formats: As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format.

So one solution is to format your date in the right format:

now <- as.POSIXlt(Sys.time())
now.str <- format(now,'%Y-%m-%d %H:%M:%S')
now.str 
[1] "2013-02-26 04:12:24"

or in this format 

format(now,'%Y%m%d%H%M%S')
[1] "20130226041224"

Then update your table using dbSendQuery. Here is an example for creating a dynamic query for any table and any date. Adapt it to your needs. 

table.Name <- "MY_TABLE"
query <-  paste(paste('UPDATE ', table.Name,sep=''),
                paste('SET datetime_column =' ,"'",now.str,"'",sep=''),
                'WHERE id=1',sep='\n')

cat(query)
UPDATE MY_TABLE
SET datetime_column ='2013-02-26 04:12:24'
WHERE id=1

dbSendQuery(con, query)

I think also that this should work with dbWritetable, once you format your dates in the right format. But I don't have MySQL installed to test it. Try it and let me know if this helps.

Martin Serrano
  • 3,727
  • 1
  • 35
  • 48
agstudy
  • 119,832
  • 17
  • 199
  • 261
  • It doesn't work with dbWriteTable, but it might work with dbSendQuery. But I'd have to break up my statements into one write per row if I did that, right? – Charles Feb 26 '13 at 04:30
  • @Charles yes if you have one date by row you need to call this for each row. Of course the numbers of updated rows depends on your Where clause. I would try to loop , and if it is slow, I would modify my sql request , to update multiple records. (You can ask new question if you can't do it, but it becomes clearly a sql question, something like [this](http://stackoverflow.com/questions/5778140/update-multiple-records-in-sql) may help – agstudy Feb 26 '13 at 04:40
1

I've been using agstudy's solution and then calling this SQL after the dbWriteTable using dbSendQuery. Seems to be working well.

ALTER TABLE TableName MODIFY DateFieldName datetime;
Timmwardion
  • 63
  • 1
  • 8