从MySQL正确导入日期到R [英] importing date correctly from MySQL into R

问题描述

我的问题与这个问题大致相同 / a>。简而言之，我正在使用dplyr（与RMySQL一起）导入带有datetime列的MySQL表，并且我想将其转换为R中的日期。在上面链接的问题中，未发布任何答案，仅由Hadley发表评论。 OP显然根据所提意见找到了解决方案。通过Hadley的评论，我了解到，可以通过将列作为日期正确导入R中来解决此问题。这样就无需使用as.Date了。但是，我找不到使用dplyr和RMySQL的方法。

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.

我不知道如何发布完全可复制的示例，因为它是MySQL数据库连接，但我会发布我的代码：

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

如果我尝试：

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

然后我运行：

pedidos

R返回值：

FUNCTION AS.DATE does not exist

就像另一个问题中的OP，我可以使用collect（）解决此问题，但随后达到了不将数据复制到内存的目的。以下是有关MySQL
DateSent中的数据类型的一些信息：datetime

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

和R sessionInfo（）：

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  

推荐答案

< >还解决了datetime列问题，不过简短的回答就是用 RMariaDB 是解决方案。

正如在此问题下提到的注释，此问题实际上已在来自0.11的RMySQL ，但唯一问题是此版本无法从官方CRAN存储库中获得。换句话说，如果您不介意通过devtools手动安装软件包，那么从RMySQL 0.11源代码进行安装正是这个问题的答案。

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.

但是如上所述在正式的自述文件中，会成为RMariaDB Datetime Support 也包含在RMariaDB软件包的主流中。更重要的是，最新版本1.0.6可从CRAN官方存储库中获得。

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.

这篇关于从MySQL正确导入日期到R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！