DBI 1.2.3 dbQuoteLiteral() POSIXct with MySQL 8

Since DBI Version 1.2.3, the format used to encode datetime objects as strings has changed:

DBI 1.2.1:

> DBI::dbQuoteLiteral(DBI::ANSI(), as.POSIXct("2023-04-05 12:34:56"))
<SQL> '20230405103456'

DBI 1.2.3:

> DBI::dbQuoteLiteral(DBI::ANSI(), as.POSIXct("2023-04-05 12:34:56"))
<SQL> '2023-04-05 12-34-56+0200'

This causes problems with our database running MySQL 8.0, since it apparently doesn't understand the timezone portion and throws an error like this:

ERROR 1292: 1292: Incorrect datetime value: '2023-04-03 12-34-56+0200' for column 'created_at' at row 1

How can I fix this? Do I have to mangle the datetime values myself, rather than leaving it to DBI?

Solution

It turns out this was indeed a bug (or rather an oversight).

Short Answer: A fix has been created, merged, and will most likely be part of the next release of the RMariaDB package.

Slightly longer Answer:

DBI provides a generic interface to relational Databases. The actual database-specific stuff happens in other packages. For MySQL connections, that's RMariaDB.

Until now, RMariaDB used the default implementation provided by DBI of dbQuoteLiteral(), since there was no need to have database-specific behaviour.
Due to the added timezonehe new default format for datetime-strings isn't compatible with MySQL any more (and only MySQL, MariaDB seems to be fine, which is why it was missed).

I've worked with the dev(s) of the RMariaDB package, and we've created a fix that restores the old datetime-format string specifically when calling the method for a MySQL connection. The fix has been accepted and merged into the main branch, so it will be part of the next release of RMariaDB (probably 1.3.3).