4

My question is pretty much the same as this one. In short, I'm using dplyr (along with RMySQL) to import a MySQL table with a datetime column, and I want to convert it to date in R. In the question linked above, no answer was posted, only a comment by Hadley. The OP apparently found a solution based on the comment made. I understood from Hadley's comment that I could solve the issue by properly importing the column as date into R. Then there would be no need to use as.Date. However, I couldn't find how to do that with dplyr and RMySQL.

I don't know how to post a fully reproducible example, because it's a MySQL database connection, but I'll post my code:

If I try:

pedidos <-  my_db %>%
tbl("pedidos") %>%
mutate(test = as.Date(DateSent))

And then I run:

pedidos

R returns:

FUNCTION AS.DATE does not exist

Like the OP in the other question, I can workaround the issue using collect(), but then it defeats the purpose of not copying the data into memory. Here´s some info about data type in MySQL DateSent is datetime

and R sessionInfo():

R version 3.3.1 (2016-06-21)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

locale:
[1] LC_COLLATE=Portuguese_Brazil.1252 
[2] LC_CTYPE=Portuguese_Brazil.1252   
[3] LC_MONETARY=Portuguese_Brazil.1252
[4] LC_NUMERIC=C                      
[5] LC_TIME=Portuguese_Brazil.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets 
[6] methods   base     

other attached packages:
[1] RMySQL_0.10.11 DBI_0.5-1      dplyr_0.5.0   
[4] digest_0.6.12 

loaded via a namespace (and not attached):
[1] lazyeval_0.2.0 magrittr_1.5   R6_2.2.0      
[4] assertthat_0.1 rsconnect_0.5  tools_3.3.1   
[7] tibble_1.2     Rcpp_0.12.8
Community
  • 1
  • 1
Manoel Galdino
  • 2,376
  • 6
  • 27
  • 40
  • 1
    What's the schema of the table you are trying to access? You can't call R functions that don't have equivalents in your data.base without out calling collect. If dplyr can't translate your mutate request into a proper SQL statement, then it can't be run without pulling the data into R. – MrFlick May 03 '17 at 13:59
  • Yep, That's my understanding. And that's why I think the solution is to import the column in the correct format. Regarding the schema, the table has a lot of columns. But the relevant column info is: field: DateSent; type: datetime; Null: yes; Default: NULL – Manoel Galdino May 03 '17 at 14:40
  • 2
    Maybe try RMySQL_0.11 or newer, [they claim to support proper datetime values](https://github.com/rstats-db/RMySQL/blob/2128ad61bb2d2503e105c4daeaa96a69b7bac888/NEWS.md#rmysql-011-1-2016-03-24) – MrFlick May 03 '17 at 15:36
  • It worked. Thank you! – Manoel Galdino May 03 '17 at 21:55

1 Answers1

3

I've coped with the datetime column problem as well, however a short answer to say, replace your current MySQL DBI Driver with RMariaDB is the solution.

As the comments mentioned under this question, this problem has actually solved in the development upstream of RMySQL from 0.11, but the only problem is this version is not available from official CRAN repository. In another words, If you don't mind installing a package manually through devtools, installation from the RMySQL 0.11 source code is exactly an answer for this question.

But as stated officially in the readme, RMariaDB package is going to be as a replacement to the RMySQL driver. Surely, the function that Datetime Support is also included in the mainstream of RMariaDB package. What's more, the newest release of Version 1.0.6 is available from CRAN official repository.

千木郷
  • 1,595
  • 2
  • 19
  • 30