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();elsereturn 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();elsereturn 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));
#elsereturn QVariant(QString::fromLatin1(val));
#endifcase QVariant::Time:
#if QT_CONFIG(datestring)return QVariant(QTime::fromString(QString::fromLatin1(val), Qt::ISODate));
#elsereturn QVariant(QString::fromLatin1(val));
#endifcase QVariant::DateTime:
#if QT_CONFIG(datestring)return QVariant(QDateTime::fromString(QString::fromLatin1(val),Qt::ISODate).toLocalTime());
#elsereturn QVariant(QString::fromLatin1(val));
#endifcase 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