sqlSave from RODBC only saves the year of a Date in R

Question

I connected R with my mysql Database. When I use the command sqlSave(channel, dataframe) my dataframe will be written into the database, but all entries which are coded as dates in R (class "POSIXct" "POSIXt", e.g. "2016-01-01 CET") only the year will be recorded in the mysql Database. How is this possible?
Thanks for your help.

EDIT: The column type is "double". So i guess sqlSave can't save as Date/Time?

Answer 1

I used the exact function, but from library(RMySQL), as follows and it worked for me. First make sure your data is in the correct format:

toSql$Date<-as.POSIXct(strptime(toSql$Date,"%Y-%m-%d %H:%M",tz="GMT"))

Then also make sure that when you created your table you used TIMESTAMP as the columntype:

create table table_name(
ID INTEGER,
DATE TIMESTAMP,
PRIMARY KEY(ID)
)
;

Saving to the database then looks as follows:

dbWriteTable(con, name = "table_name", toSql, overwrite=FALSE,   
              append=TRUE,row.names = NA)

Perhaps also make sure that your names of your dataframe is the same:

names(toSql)
[1] "ID"  "DATE"