SQL/PostgreSQL: Make sure the server returns datetime in UTC

The postgresql server by default returns the datetime in it's local
timezone. This works as long as this is the same as on the client. If
they are different, the parsing is going wrong.. Therefore let the
server return the datetime in UTC. Also do not convert the datetime into
local time to be in sync with the MySQL plugin.

[ChangeLog][SQL][PostgreSQL] Fixed a bug where a wrong QDateTime might
be returned when the PostgreSQL server and the Qt client had different
time zones configured.

Fixes: QTBUG-115960
Change-Id: I1a6dda69359a34b99ef399b2a54f35c8ba041326
Reviewed-by: Thiago Macieira <thiago.macieira@intel.com>

1 files changed, 17 insertions, 3 deletions

diff --git a/src/plugins/sqldrivers/psql/qsql_psql.cpp b/src/plugins/sqldrivers/psql/qsql_psql.cpp
index 76e3d427b0..77a6db309d 100644
--- a/src/plugins/sqldrivers/psql/qsql_psql.cpp
+++ b/src/plugins/sqldrivers/psql/qsql_psql.cpp
@@ -140,6 +140,7 @@ public:
     bool setEncodingUtf8();
     void setDatestyle();
     void setByteaOutput();
+    void setUtcTimeZone();
     void detectBackslashEscape();
     mutable QHash<int, QString> oidToTable;
 };
@@ -646,9 +647,12 @@ QVariant QPSQLResult::data(int i)
         return QVariant(QDate::fromString(QString::fromLatin1(val), Qt::ISODate));
     case QMetaType::QTime:
         return QVariant(QTime::fromString(QString::fromLatin1(val), Qt::ISODate));
-    case QMetaType::QDateTime:
-        return QVariant(QDateTime::fromString(QString::fromLatin1(val),
-                                              Qt::ISODate).toLocalTime());
+    case QMetaType::QDateTime: {
+        QString tzString(QString::fromLatin1(val));
+        if (!tzString.endsWith(u'Z'))
+            tzString.append(u'Z');       // make UTC
+        return QVariant(QDateTime::fromString(tzString, Qt::ISODate));
+    }
 #else
     case QMetaType::QDate:
     case QMetaType::QTime:
@@ -927,6 +931,15 @@ void QPSQLDriverPrivate::setByteaOutput()
     }
 }
 
+void QPSQLDriverPrivate::setUtcTimeZone()
+{
+    PGresult *result = exec("SET TIME ZONE 'UTC'");
+    int status = PQresultStatus(result);
+    if (status != PGRES_COMMAND_OK)
+        qWarning() << QString::fromUtf8(PQerrorMessage(connection));
+    PQclear(result);
+}
+
 void QPSQLDriverPrivate::detectBackslashEscape()
 {
     // standard_conforming_strings option introduced in 8.2
@@ -1192,6 +1205,7 @@ bool QPSQLDriver::open(const QString &db,
     }
     d->setDatestyle();
     d->setByteaOutput();
+    d->setUtcTimeZone();
 
     setOpen(true);
     setOpenError(false);