Fix storing of QDateTime timespec for Sqlite

Commit 9e64fc9e1cebf1e11694c4f536881128f5aee288 caused a regression
which stored all QDateTime entries as if they were in localtime,
which causes them to be offset by the amount of local timezone
offset. This is fixed by adding "Z" if the time should be in UTC or
using "+/-hh:mm" if it should use fixed UTC offset or specific
timezone.

Task-number: QTBUG-57138
Change-Id: Ie60905dfb3a517db442b636ca41daf8348753d84
Reviewed-by: Edward Welbourne <edward.welbourne@qt.io>
Reviewed-by: Andy Shaw <andy.shaw@qt.io>
(cherry picked from commit 0a5f71c6062d575602ff041fb1b88ec2d8ad92bc)

2 files changed, 66 insertions, 1 deletions

diff --git a/src/sql/drivers/sqlite/qsql_sqlite.cpp b/src/sql/drivers/sqlite/qsql_sqlite.cpp
index 4286f5b338..a280d4b3ca 100644
--- a/src/sql/drivers/sqlite/qsql_sqlite.cpp
+++ b/src/sql/drivers/sqlite/qsql_sqlite.cpp
@@ -45,6 +45,7 @@
 #include <qstringlist.h>
 #include <qvector.h>
 #include <qdebug.h>
+#include <QTimeZone>
 
 #if defined Q_OS_WIN
 # include <qt_windows.h>
@@ -397,6 +398,32 @@ bool QSQLiteResult::prepare(const QString &query)
     return true;
 }
 
+static QString secondsToOffset(int seconds)
+{
+    const QChar sign = ushort(seconds < 0 ? '-' : '+');
+    seconds = qAbs(seconds);
+    const int hours = seconds / 3600;
+    const int minutes = (seconds % 3600) / 60;
+
+    return QString(QStringLiteral("%1%2:%3")).arg(sign).arg(hours, 2, 10, QLatin1Char('0')).arg(minutes, 2, 10, QLatin1Char('0'));
+}
+
+static QString timespecToString(const QDateTime &dateTime)
+{
+    switch (dateTime.timeSpec()) {
+    case Qt::LocalTime:
+        return QString();
+    case Qt::UTC:
+        return QStringLiteral("Z");
+    case Qt::OffsetFromUTC:
+        return secondsToOffset(dateTime.offsetFromUtc());
+    case Qt::TimeZone:
+        return secondsToOffset(dateTime.timeZone().offsetFromUtc(dateTime));
+    default:
+        return QString();
+    }
+}
+
 bool QSQLiteResult::exec()
 {
     const QVector<QVariant> values = boundValues();
@@ -442,7 +469,7 @@ bool QSQLiteResult::exec()
                     break;
                 case QVariant::DateTime: {
                     const QDateTime dateTime = value.toDateTime();
-                    const QString str = dateTime.toString(QStringLiteral("yyyy-MM-ddThh:mm:ss.zzz"));
+                    const QString str = dateTime.toString(QStringLiteral("yyyy-MM-ddThh:mm:ss.zzz") + timespecToString(dateTime));
                     res = sqlite3_bind_text16(d->stmt, i + 1, str.utf16(),
                                               str.size() * sizeof(ushort), SQLITE_TRANSIENT);
                     break;
diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index ba3cfb243a..f20ed6925f 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -248,6 +248,9 @@ private slots:
     void integralTypesMysql_data() { generic_data("QMYSQL"); }
     void integralTypesMysql();
 
+    void QTBUG_57138_data() { generic_data("QSQLITE"); }
+    void QTBUG_57138();
+
 private:
     // returns all database connections
     void generic_data(const QString &engine=QString());
@@ -4076,5 +4079,40 @@ void tst_QSqlQuery::integralTypesMysql()
     }
 }
 
+void tst_QSqlQuery::QTBUG_57138()
+{
+    QDateTime utc = QDateTime(QDate(2150, 1, 5), QTime(14, 0, 0, 123), Qt::UTC);
+    QDateTime localtime = QDateTime(QDate(2150, 1, 5), QTime(14, 0, 0, 123), Qt::LocalTime);
+    QDateTime tzoffset = QDateTime(QDate(2150, 1, 5), QTime(14, 0, 0, 123), Qt::OffsetFromUTC, 3600);
+
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+
+    QSqlQuery create(db);
+    QString tableName = qTableName("qtbug57138", __FILE__, db);
+
+    QVERIFY_SQL(create, exec("create table " + tableName + " (id int, dt_utc datetime, dt_lt datetime, dt_tzoffset datetime)"));
+    QVERIFY_SQL(create, prepare("insert into " + tableName + " (id, dt_utc, dt_lt, dt_tzoffset) values (?, ?, ?, ?)"));
+
+    create.addBindValue(0);
+    create.addBindValue(utc);
+    create.addBindValue(localtime);
+    create.addBindValue(tzoffset);
+
+    QVERIFY_SQL(create, exec());
+
+    QSqlQuery q(db);
+    q.prepare("SELECT dt_utc, dt_lt, dt_tzoffset FROM " + tableName + " WHERE id = ?");
+    q.addBindValue(0);
+
+    QVERIFY_SQL(q, exec());
+    QVERIFY(q.next());
+
+    QCOMPARE(q.value(0).toDateTime(), utc);
+    QCOMPARE(q.value(1).toDateTime(), localtime);
+    QCOMPARE(q.value(2).toDateTime(), tzoffset);
+}
+
 QTEST_MAIN( tst_QSqlQuery )
 #include "tst_qsqlquery.moc"