Qt 关于获取postgres time with time zone类型字段存在无效值的情况

Qt 获取 postgres time with time zone类型字段存在无效值的情况

事件起因

在使用Qt获取pg数据库中time with time zone类型字段时偶发出现时间获取失败,体现为获取结果为无效值

QVariant vt = sqlQuery->value(i);
// QVariant(QTime, QTime(Invalid))

查看源码

查看Qt qsql_psql.cpp源码

QVariant QPSQLResult::data(int i)
{
    Q_D(const QPSQLResult);
    if (i >= PQnfields(d->result)) {
        qWarning("QPSQLResult::data: column %d out of range", i);
        return QVariant();
    }
    const int currentRow = isForwardOnly() ? 0 : at();
    int ptype = PQftype(d->result, i);
    QVariant::Type type = qDecodePSQLType(ptype);
    if (PQgetisnull(d->result, currentRow, i))
        return QVariant(type);
        
    const char *val = PQgetvalue(d->result, currentRow, i);
    switch (type) {
    case QVariant::Bool:
        return QVariant((bool)(val[0] == 't'));
    case QVariant::String:
        return d->drv_d_func()->isUtf8 ? QString::fromUtf8(val) : QString::fromLatin1(val);
    case QVariant::LongLong:
        if (val[0] == '-')
            return QByteArray::fromRawData(val, qstrlen(val)).toLongLong();
        else
            return QByteArray::fromRawData(val, qstrlen(val)).toULongLong();
    case QVariant::Int:
        return atoi(val);
    case QVariant::Double: {
        if (ptype == QNUMERICOID) {
            if (numericalPrecisionPolicy() == QSql::HighPrecision)
                return QString::fromLatin1(val);
        }
        bool ok;
        double dbl = qstrtod(val, nullptr, &ok);
        if (!ok) {
            if (qstricmp(val, "NaN") == 0)
                dbl = qQNaN();
            else if (qstricmp(val, "Infinity") == 0)
                dbl = qInf();
            else if (qstricmp(val, "-Infinity") == 0)
                dbl = -qInf();
            else
                return QVariant();
        }
        if (ptype == QNUMERICOID) {
            if (numericalPrecisionPolicy() == QSql::LowPrecisionInt64)
                return QVariant((qlonglong)dbl);
            else if (numericalPrecisionPolicy() == QSql::LowPrecisionInt32)
                return QVariant((int)dbl);
            else if (numericalPrecisionPolicy() == QSql::LowPrecisionDouble)
                return QVariant(dbl);
        }
        return dbl;
    }
    case QVariant::Date:
#if QT_CONFIG(datestring)
        return QVariant(QDate::fromString(QString::fromLatin1(val), Qt::ISODate));
#else
        return QVariant(QString::fromLatin1(val));
#endif
    case QVariant::Time:
#if QT_CONFIG(datestring)
        return QVariant(QTime::fromString(QString::fromLatin1(val), Qt::ISODate));
#else
        return QVariant(QString::fromLatin1(val));
#endif
    case QVariant::DateTime:
#if QT_CONFIG(datestring)
        return QVariant(QDateTime::fromString(QString::fromLatin1(val),
                                              Qt::ISODate).toLocalTime());
#else
        return QVariant(QString::fromLatin1(val));
#endif
    case QVariant::ByteArray: {
        size_t len;
        unsigned char *data = PQunescapeBytea((const unsigned char*)val, &len);
        QByteArray ba(reinterpret_cast<const char *>(data), int(len));
        qPQfreemem(data);
        return QVariant(ba);
    }
    default:
    case QVariant::Invalid:
        qWarning("QPSQLResult::data: unknown data type");
    }
    return QVariant();
}

发现当时间戳样式为:14:52:21.202000+08:00时,
会导致PQgetvalue(d->result, currentRow, i)函数返回:14:52:21.202+08,
此时return QVariant(QTime::fromString(QString::fromLatin1(val), Qt::ISODate));
将会返回Invalid,
原因应该是QTime::fromString内部并没有处理这种带有时区样式但是毫秒精度为3位的情况,
同时通过PQgetvalue函数返回的时区格式也存在问题

解决办法

将time with time zone字段类型查询时变为time without time zone