Search code examples
mysqlrrodbc

posix written through rodbc into mysql database gets truncated

When I write a column of class POSIXct into a MySQL database using RODBC it gets truncated to only the year. This happens for example with:

sqlSave(connection, dat = data.frame(date = as.POSIXct("2015-01-01 08:10:00")+0:10*60), 
    tablename = "date_column")

How can I avoid this?

Solution

  • You probably need to make the column of type datetime in mysql explicitly like this: 

    library(RODBC)
con <- odbcConnect("mysql", uid="root", case = "tolower")
(dat <- data.frame(date = Sys.time()))
#                  date
# 1 2015-03-08 23:55:33
res <- sqlSave(con, dat = dat, varTypes = c("date" = "datetime"), tablename = "date_column")
close(con)