TIMESTAMP is incorrect CAST type for MySQL & MariaDB

[avsdev-cw]

Similar to: #1011

DATETIME is the correct cast type:
https://mariadb.com/kb/en/cast/
https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast

[avsdev-cw]

Further to this, the correct datetime format for mysql and mariadb differs from the default:

sql_escape_datetime.DBIConnection <- function(con, x) {
  x <- strftime(x, "%Y-%m-%dT%H:%M:%OSZ", tz = "UTC")
  sql_escape_string(con, x)
}

The correct format according to the docs (https://dev.mysql.com/doc/refman/8.0/en/datetime.html) is:

sql_escape_datetime.MariaDBConnection <- function(con, x) {
  x <- strftime(x, "%Y-%m-%d %H:%M:%OS", tz = "UTC")
  sql_escape_string(con, x)
}

[avsdev-cw]

Also related, (see the links in the first post), DOUBLE/FLOAT is the correct CAST type for numeric, not NUMERIC.

[mgirlich]

Do you want to create a PR for this? 😄

[avsdev-cw]

Done 😄