John Smith John Smith - 10 months ago 44
MySQL Question

Using Shiny to insert records into a MYSQL DB

I wish to write back to a mysql database based on a shiny app.
This code with some modifications come from the excellent 1. I added a portion to give me the insertion timestamp of the record. When i try to insert the record i get the following error

Error in .local(conn, statement, ...) :
could not run statement: Incorrect datetime value: '1473931679.54479' for column 'insert_dtm' at row 1

This datetime value is generated by
Add insertion time stamp of the record and re-order columns
of my code. Can anyone explain why this is happening.

I should mention in the table that is being written to the datatype is
but i can change this to anything as all im interested in is keeping track of when the records were inserted

save_data <- function(data) {
# Connect to the database
db <- dbConnect(MySQL(), dbname = databaseName, host =
port = options()$mysql$port, user = options()$mysql$user,
password = options()$mysql$password)

# Add insertion time stamp of the record and re-order columns
data <- data %>%
mutate(insert_dtm = Sys.time()) %>%
select (insert_dtm, everything()) %>%

# Construct the update query by looping over the data fields
query <- sprintf(foo)

# Submit the update query and disconnect
dbGetQuery(db, query)

Answer Source

The reason why it didn't work is because of the difference between the date formats of Sys.time() and MySQL date or datetime types.

> Sys.time()
###[1] "2016-09-23 11:30:36 CEST"
###MySQL datetime: "2016-09-23 11:30:36"

After converting to MySQL datetime format it should work. I think the problem is the timezone.

For YYYY-MM-DD format

> format(Sys.time(), "%Y-%m-%d")
###[1] "2016-09-23"

For YYYY-MM-DD hh:mm:ss format

> format(Sys.time(), "%Y-%m-%d %H:%M:%S")
###[1] "2016-09-23 11:37:45"