MySQL not getting the TIMESTAMP fields?

[beanumber]

> dbDataType(ANSI(), as.POSIXct("2012-01-03T00:30:00Z"))
[1] "TIMESTAMP"

[beanumber]

@liwencong1995 to submit a MWE.

[liwencong1995]

First, create a new database called "macleish" by running the command below in your terminal.

mysql> CREATE DATABASE macleish;

Then, run the following command in R with your user name and password.

db <- src_mysql(dbname = "macleish", host = "localhost",
                user = "your_username", password = "your_password")
macleish <- etl("macleish", db, dir = "~/Desktop/macleish")

macleish %>%
  etl_create()
macleish %>%
  tbl("orchard") %>%
  collect() %>%
  head()

Source: local data frame [6 x 9]

                  when temperature wind_speed wind_dir rel_humidity pressure par_density par_total
                 (chr)       (dbl)      (dbl)    (dbl)        (dbl)    (dbl)       (dbl)     (dbl)
1 2014-06-27T16:00:00Z       24.19      1.055    12.76        47.24    53.76       4.112     0.662
2 2014-06-27T16:10:00Z       24.13      0.580    73.80        48.33  1021.00       4.207     0.678
3 2014-06-27T16:20:00Z       24.06      0.672    38.15        50.25  1021.00       3.934     0.634
4 2014-06-27T16:30:00Z       23.97      0.774    43.20        51.36  1021.00       3.989     0.643
5 2014-06-27T16:40:00Z       23.63      0.411    68.69        56.45  1021.00       3.825     0.616
6 2014-06-27T16:50:00Z       23.28      0.325    95.40        57.48  1021.00       4.372     0.704
Variables not shown: rainfall (dbl)

[beanumber]

Hmm...

Something like this doesn't work, because when is a DATETIME already in MySQL:

macleish %>%
  tbl("orchard") %>% 
  mutate(timestamp = STR_TO_DATE(when, "%Y-%m-%d %h:%i:%s"))

This works, but is not great since it forces collect():

macleish %>%
  tbl("orchard") %>% 
  collect() %>% 
  mutate(timestamp = as_datetime(when))

There has to be a better solution...