From aa93b4835a966be021a8f8a4d1872a7fd606c5d4 Mon Sep 17 00:00:00 2001 From: Thiago Macieira Date: Mon, 9 Aug 2021 18:02:47 -0700 Subject: 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 --- src/plugins/sqldrivers/mysql/qsql_mysql.cpp | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'src/plugins/sqldrivers/mysql/qsql_mysql.cpp') 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); } -- cgit v1.2.3