MySQL: don't format QDateTime with timezones

Neither MySQL nor MariaDB like it. According to the documentation[1],
MySQL now accepts timezones using the [+-]HH:MM format (and -00:00 is
rejected). MariaDB does not accept timezones at all[2].

This has apparently been broken since Qt 5.0 (the "Z" suffix was
introduced in commit 2528f4ffe53dfbd640249f63497522929264f3d7), but this
issue was never noticed because the of prepared queries: when they're in
use, we transfer the time using a MYSQL_TIME structure, which does not
support timezone offsets either. We've only noticed this issue when the
code to determine if the MySQL client library supported prepared
statements broke.

[1] https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html
[2] https://mariadb.com/kb/en/date-and-time-literals/

Task-number: QTBUG-95071
Change-Id: I4a40ccbd3321467a8429fffd1699cc4c050ae746
Reviewed-by: Andy Shaw <andy.shaw@qt.io>

1 files changed, 14 insertions, 0 deletions

diff --git a/src/plugins/sqldrivers/mysql/qsql_mysql.cpp b/src/plugins/sqldrivers/mysql/qsql_mysql.cpp
index 5358d6a94e..ed304d807d 100644
--- a/src/plugins/sqldrivers/mysql/qsql_mysql.cpp
+++ b/src/plugins/sqldrivers/mysql/qsql_mysql.cpp
@@ -1465,6 +1465,20 @@ QString QMYSQLDriver::formatValue(const QSqlField &field, bool trimStrings) cons
                 qWarning("QMYSQLDriver::formatValue: Database not open");
             }
             Q_FALLTHROUGH();
+        case QMetaType::QDateTime:
+            if (QDateTime dt = field.value().toDateTime(); dt.isValid()) {
+                // MySQL format doesn't like the "Z" at the end, but does allow
+                // "+00:00" starting in version 8.0.19. However, if we got here,
+                // it's because the MySQL server is too old for prepared queries
+                // in the first place, so it won't understand timezones either.
+                // Besides, MYSQL_TIME does not support timezones, so match it.
+                r = QLatin1Char('\'') +
+                        dt.date().toString(Qt::ISODate) +
+                        QLatin1Char('T') +
+                        dt.time().toString(Qt::ISODate) +
+                        QLatin1Char('\'');
+            }
+            break;
         default:
             r = QSqlDriver::formatValue(field, trimStrings);
         }