Date columns

[ggrothendieck]

RMySQL seems to use text for Date (and POSIXct) columns. It should use MySQL date type and datetime or timestamp type and convert correctly in both directions.

library(RMySQL)

t1 <- data.frame(d = as.Date("2001-01-30"))
con <- dbConnect("MySQL", "test")
dbWriteTable(con, "t1", t1, row.names = FALSE)
dbGetQuery(con, "describe t1")

which produces:

  Field Type Null Key Default Extra
1     d text  YES        <NA>

but d should be of MySQL date type, not text.

packageVersion("RMySQL")
[1] ‘0.9.3’
packageVersion("DBI")
[1] ‘0.3.1’
R.version.string
[1] "R version 3.1.1 Patched (2014-08-21 r66456)"

[hadley]

This should be a straightforward fix to dbDataType(), although making sure the dates are correctly loaded back into R might be harder.

[elbamos]

Came to raise the same issue, but its already here, so I guess this is a +1.

[hadley]

In the dev version, DATETIMEs and TIMESTAMPs are retrieved as POSIXct, and DATEs are retrieved as Dates. Will have similar code for sending shortly

[elbamos]

Thanks Hadley - please let me know how I can help.

On Feb 17, 2015, at 10:44 AM, Hadley Wickham notifications@github.com wrote:

In the dev version, DATETIMEs and TIMESTAMPs are retrieved as POSIXct, and DATEs are retrieved as Dates. Will have similar code for sending shortly

—
Reply to this email directly or view it on GitHub.

[hadley]

Dealing with timezones was tricky, but I'm pretty sure it's correct now.

[ywhcuhk]

run into the same problem. Date column from R data frame is still loaded as text if I send the dataframe to Mysql. My RMySQL version is 0.10.3

[ChristopherCosler]

Experiencing the same problem as ywhuofu. Has it not been fixed or are we using it wrong?

[martinschmelzer]

Might be experiencing the same (or at least closely related) issue. I write POSIXct objects into a DATETIME column. In phpMyAdmin everything looks fine. When I retrieve the data with

q <- dbSendQuery(con, "SELECT * FROM table LIMIT 10")
test <- dbFetch(q)

The DATETIME column only contains the value 1970-01-01 00:59:59 all over the table. Same applies to a column of type TIMESTAMP.