John Smith John Smith - 2 months ago 10
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
DATETIME
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 =
options()$mysql$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)
dbDisconnect(db)
}

GyD GyD
Answer

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"