Added timezone support for datetime fields in PSQL

This patch adds correct timezone support in PSQL plugin. Prior to this
patch, no timezone support was provided, so only the following case
worked :
 * using local time in both client application and postgresql server
 * datetime were using second precision

This patch tries to take care that postgresql has two different
datatypes for date time, respectively :
 * timestamp with time zone
 * timestamp without time zone

Both are internally stored as UTC values, but are not parsed the same.
 * timestamp with time zone assumes that there is a time zone
   information and will parse date time accordingly, and then, convert
   into UTC before storing them
 * timestamp without time zone assumes that there is no time zone
   information and will silently ignore any, unless the datetime is
   explicitly specified as having a time zone, in case it will convert
   it into UTC before storing it

Both are retrieved as local time values, with the following difference
 * timestamp with time zone includes the timezone information
   (2014-02-12 10:20:12+0100 for example)
 * timestamp without time zone does not include it

The patch does the following :
 * parse the date retrieved by postgresql server using QDateTime
   functions, which work correctly
 * always convert the date to UTC before giving it to postgresql
 * force time zone so that timezone information is taken into account
   by postgresql
 * also adds the milliseconds when storing QDateTime values

The following configurations are tested to work :
 * client and server using same timezone, timestamp with or without tz
 * client and server using different timezone, timestamp with tz

The following configuration will *not* work :
 * client and server using different timezones, timestamp without tz

Because data will be converted to local time by the postgresql server,
so when returned it will be different from what had been serialized.
Prior to this patch, it gave the illusion to work because since TZ
information was lost, time was stored as local time from postgresql.
Lots of inconsistencies occurred, though, in case client tz changes...

I don't expect this to be an issue since having different TZ in server
and client and *not* handling this is a broken setup anyway.

Almost based on changes proposed by julien.blanc@nmc-company.fr

[ChangeLog][QtSql] Added timezone support for datetime fields in PSQL

Task-number: QTBUG-36211
Change-Id: I5650a5ef60cb3f14f0ab619825612831c7e90c12
Reviewed-by: Mark Brand <mabrand@mabrand.nl>

1 files changed, 17 insertions, 26 deletions

diff --git a/src/sql/drivers/psql/qsql_psql.cpp b/src/sql/drivers/psql/qsql_psql.cpp
index e76dcc26a0..4268ea06f6 100644
--- a/src/sql/drivers/psql/qsql_psql.cpp
+++ b/src/sql/drivers/psql/qsql_psql.cpp
@@ -426,11 +426,8 @@ QVariant QPSQLResult::data(int i)
 #ifndef QT_NO_DATESTRING
         if (str.isEmpty())
             return QVariant(QTime());
-        if (str.at(str.length() - 3) == QLatin1Char('+') || str.at(str.length() - 3) == QLatin1Char('-'))
-            // strip the timezone
-            // TODO: fix this when timestamp support comes into QDateTime
-            return QVariant(QTime::fromString(str.left(str.length() - 3), Qt::ISODate));
-        return QVariant(QTime::fromString(str, Qt::ISODate));
+        else
+            return QVariant(QTime::fromString(str, Qt::ISODate));
 #else
         return QVariant(str);
 #endif
@@ -438,19 +435,13 @@ QVariant QPSQLResult::data(int i)
     case QVariant::DateTime: {
         QString dtval = QString::fromLatin1(val);
 #ifndef QT_NO_DATESTRING
-        if (dtval.length() < 10)
-            return QVariant(QDateTime());
-        // remove the timezone
-        // TODO: fix this when timestamp support comes into QDateTime
-        if (dtval.at(dtval.length() - 3) == QLatin1Char('+') || dtval.at(dtval.length() - 3) == QLatin1Char('-'))
-            dtval.chop(3);
-        // milliseconds are sometimes returned with 2 digits only
-        if (dtval.at(dtval.length() - 3).isPunct())
-            dtval += QLatin1Char('0');
-        if (dtval.isEmpty())
+        if (dtval.length() < 10) {
             return QVariant(QDateTime());
-        else
-            return QVariant(QDateTime::fromString(dtval, Qt::ISODate));
+       } else {
+            QChar sign = dtval[dtval.size() - 3];
+            if (sign == QLatin1Char('-') || sign == QLatin1Char('+')) dtval += QLatin1String(":00");
+            return QVariant(QDateTime::fromString(dtval, Qt::ISODate).toLocalTime());
+        }
 #else
         return QVariant(dtval);
 #endif
@@ -536,6 +527,11 @@ QSqlRecord QPSQLResult::record() const
         int precision = PQfmod(d->result, i);
 
         switch (ptype) {
+        case QTIMESTAMPOID:
+        case QTIMESTAMPTZOID:
+            precision = 3;
+            break;
+
         case QNUMERICOID:
             if (precision != -1) {
                 len = (precision >> 16);
@@ -1263,15 +1259,10 @@ QString QPSQLDriver::formatValue(const QSqlField &field, bool trimStrings) const
         case QVariant::DateTime:
 #ifndef QT_NO_DATESTRING
             if (field.value().toDateTime().isValid()) {
-                QDate dt = field.value().toDateTime().date();
-                QTime tm = field.value().toDateTime().time();
-                // msecs need to be right aligned otherwise psql interprets them wrong
-                r = QLatin1Char('\'') + QString::number(dt.year()) + QLatin1Char('-')
-                          + QString::number(dt.month()).rightJustified(2, QLatin1Char('0')) + QLatin1Char('-')
-                          + QString::number(dt.day()).rightJustified(2, QLatin1Char('0')) + QLatin1Char(' ')
-                          + tm.toString() + QLatin1Char('.')
-                          + QString::number(tm.msec()).rightJustified(3, QLatin1Char('0'))
-                          + QLatin1Char('\'');
+                // we force the value to be considered with a timezone information, and we force it to be UTC
+                // this is safe since postgresql stores only the UTC value and not the timezone offset (only used
+                // while parsing), so we have correct behavior in both case of with timezone and without tz
+                r = QLatin1String("TIMESTAMP WITH TIME ZONE ") + QLatin1Char('\'') + field.value().toDateTime().toUTC().toString(QLatin1String("yyyy-MM-ddThh:mm:ss.zzz")) + QLatin1Char('Z') + QLatin1Char('\'');
             } else {
                 r = QLatin1String("NULL");
             }