search

ankane / dbx

A fast, easy-to-use database library for R
Other
187 stars 15 forks source link

UTC being passed in Upsert #3

Closed cspenn closed 6 years ago

cspenn commented 6 years ago

When sending a date back to MySQL, errors out saying incorrect date value.

Sys.setenv(TZ = "America/New_York")

TZ is set via environment variable. MySQL database is local, 8.0 on machine.

The data is in MySQL format in the dataframe:

ddf$thedate
...
[991] "2018-03-29" "2016-09-03" "2018-02-04" "2008-02-21" "2016-01-20" "2016-03-03" "2013-04-01" "2014-08-16" "2011-08-15"
[1000] "2011-12-10"

Error output:

> upsert <- dbxUpsert(dbxcon, "tablename", ddf, where_cols = c("id"))
Error in .local(conn, statement, ...) : 
  could not run statement: Incorrect date value: '2016-05-07 UTC' for column 'thedate' at row 1
ankane commented 6 years ago

Hey @cspenn, thanks for reporting 👍 It looks like an issue with typecasting. I'll see how to fix, but in the meantime, you should be able to cast the column to a string before upserting.

ankane commented 6 years ago

Should be fixed on master. Also added tests for it.

devtools::install_github("ankane/dbx")