OCI: Use TIMESTAMP for datetime data

The TIMESTAMP type has been available in Oracle since Oracle 9i
which was released in June 2001 and contains more data than the
DATE type so it can be reliably used for the related data types.

This adds support for preserving milliseconds and the time zone
information if this is passed or in the database.

[ChangeLog][QtSql][OCI] Added support for the TIMESTAMP data type.

Task-number: QTBUG-23
Change-Id: Icf7a012dda75fb342ce6c6aa34eaa2a52755ff2d
Reviewed-by: Edward Welbourne <edward.welbourne@qt.io>

2 files changed, 266 insertions, 98 deletions

diff --git a/src/plugins/sqldrivers/oci/qsql_oci.cpp b/src/plugins/sqldrivers/oci/qsql_oci.cpp
index b6c74b355d..de47df10ce 100644
--- a/src/plugins/sqldrivers/oci/qsql_oci.cpp
+++ b/src/plugins/sqldrivers/oci/qsql_oci.cpp
@@ -55,6 +55,7 @@
 #include <qvarlengtharray.h>
 #include <qvector.h>
 #include <qdebug.h>
+#include <qtimezone.h>
 
 // This is needed for oracle oci when compiling with mingw-w64 headers
 #if defined(__MINGW64_VERSION_MAJOR) && defined(_WIN64)
@@ -112,9 +113,6 @@ static const ub2 qOraCharset = OCI_UCS2ID;
 typedef QVarLengthArray<sb2, 32> IndicatorArray;
 typedef QVarLengthArray<ub2, 32> SizeArray;
 
-static QByteArray qMakeOraDate(const QDateTime& dt);
-static QDateTime qMakeDate(const char* oraDate);
-
 static QByteArray qMakeOCINumber(const qlonglong &ll, OCIError *err);
 static QByteArray qMakeOCINumber(const qulonglong& ull, OCIError* err);
 
@@ -156,6 +154,60 @@ QOCIRowId::~QOCIRowId()
         OCIDescriptorFree(id, OCI_DTYPE_ROWID);
 }
 
+class QOCIDateTime
+{
+public:
+    QOCIDateTime(OCIEnv *env, OCIError *err, const QDateTime &dt = QDateTime());
+    ~QOCIDateTime();
+    OCIDateTime *dateTime;
+    static QDateTime fromOCIDateTime(OCIEnv *env, OCIError *err, OCIDateTime *dt);
+};
+
+QOCIDateTime::QOCIDateTime(OCIEnv *env, OCIError *err, const QDateTime &dt)
+    : dateTime(nullptr)
+{
+    OCIDescriptorAlloc(env, reinterpret_cast<void**>(&dateTime), OCI_DTYPE_TIMESTAMP_TZ, 0, 0);
+    if (dt.isValid()) {
+        const QDate date = dt.date();
+        const QTime time = dt.time();
+        // Zone in +hh:mm format (stripping UTC prefix from OffsetName)
+        QString timeZone = dt.timeZone().displayName(dt, QTimeZone::OffsetName).mid(3);
+        const OraText *tz = reinterpret_cast<const OraText *>(timeZone.utf16());
+        OCIDateTimeConstruct(env, err, dateTime, date.year(), date.month(), date.day(), time.hour(),
+                             time.minute(), time.second(), time.msec() * 1000000,
+                             const_cast<OraText *>(tz), timeZone.length() * sizeof(QChar));
+    }
+}
+
+QOCIDateTime::~QOCIDateTime()
+{
+    if (dateTime != nullptr)
+        OCIDescriptorFree(dateTime, OCI_DTYPE_TIMESTAMP_TZ);
+}
+
+QDateTime QOCIDateTime::fromOCIDateTime(OCIEnv *env, OCIError *err, OCIDateTime *dateTime)
+{
+    sb2 year;
+    ub1 month, day, hour, minute, second;
+    ub4 nsec;
+    sb1 tzHour, tzMinute;
+
+    OCIDateTimeGetDate(env, err, dateTime, &year, &month, &day);
+    OCIDateTimeGetTime(env, err, dateTime, &hour, &minute, &second, &nsec);
+    OCIDateTimeGetTimeZoneOffset(env, err, dateTime, &tzHour, &tzMinute);
+    int secondsOffset = (qAbs(tzHour) * 60 + tzMinute) * 60;
+    if (tzHour < 0)
+        secondsOffset = -secondsOffset;
+    // OCIDateTimeGetTime gives "fractions of second" as nanoseconds
+    return QDateTime(QDate(year, month, day), QTime(hour, minute, second, nsec / 1000000),
+                     Qt::OffsetFromUTC, secondsOffset);
+}
+
+struct TempStorage {
+    QList<QByteArray> rawData;
+    QList<QOCIDateTime *> dateTimes;
+};
+
 typedef QSharedDataPointer<QOCIRowId> QOCIRowIdPointer;
 QT_BEGIN_INCLUDE_NAMESPACE
 Q_DECLARE_METATYPE(QOCIRowIdPointer)
@@ -227,11 +279,11 @@ public:
 
     void setStatementAttributes();
     int bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, int pos,
-                  const QVariant &val, dvoid *indPtr, ub2 *tmpSize, QList<QByteArray> &tmpStorage);
+                  const QVariant &val, dvoid *indPtr, ub2 *tmpSize, TempStorage &tmpStorage);
     int bindValues(QVector<QVariant> &values, IndicatorArray &indicators, SizeArray &tmpSizes,
-                   QList<QByteArray> &tmpStorage);
+                   TempStorage &tmpStorage);
     void outValues(QVector<QVariant> &values, IndicatorArray &indicators,
-                   QList<QByteArray> &tmpStorage);
+                   TempStorage &tmpStorage);
     inline bool isOutValue(int i) const
     { Q_Q(const QOCIResult); return q->bindValueType(i) & QSql::Out; }
     inline bool isBinaryValue(int i) const
@@ -304,7 +356,7 @@ void QOCIResultPrivate::setStatementAttributes()
 }
 
 int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, int pos,
-                   const QVariant &val, dvoid *indPtr, ub2 *tmpSize, QList<QByteArray> &tmpStorage)
+                                 const QVariant &val, dvoid *indPtr, ub2 *tmpSize, TempStorage &tmpStorage)
 {
     int r = OCI_SUCCESS;
     void *data = const_cast<void *>(val.constData());
@@ -322,14 +374,15 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
     case QVariant::Time:
     case QVariant::Date:
     case QVariant::DateTime: {
-        QByteArray ba = qMakeOraDate(val.toDateTime());
+        QOCIDateTime *ptr = new QOCIDateTime(env, err, val.toDateTime());
         r = OCIBindByPos(sql, hbnd, err,
                          pos + 1,
-                         ba.data(),
-                         ba.size(),
-                         SQLT_DAT, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
-        tmpStorage.append(ba);
-        break; }
+                         &ptr->dateTime,
+                         sizeof(OCIDateTime *),
+                         SQLT_TIMESTAMP_TZ, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
+        tmpStorage.dateTimes.append(ptr);
+        break;
+    }
     case QVariant::Int:
         r = OCIBindByPos(sql, hbnd, err,
                          pos + 1,
@@ -356,7 +409,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
                            ba.data(),
                            ba.size(),
                            SQLT_VNU, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
-        tmpStorage.append(ba);
+        tmpStorage.rawData.append(ba);
         break;
     }
     case QVariant::ULongLong:
@@ -367,7 +420,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
                            ba.data(),
                            ba.size(),
                            SQLT_VNU, indPtr, 0, 0, 0, 0, OCI_DEFAULT);
-        tmpStorage.append(ba);
+        tmpStorage.rawData.append(ba);
         break;
     }
     case QVariant::Double:
@@ -437,7 +490,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
         }
         if (r == OCI_SUCCESS)
             setCharset(*hbnd, OCI_HTYPE_BIND);
-        tmpStorage.append(ba);
+        tmpStorage.rawData.append(ba);
         break;
     } // default case
     } // switch
@@ -447,7 +500,7 @@ int QOCIResultPrivate::bindValue(OCIStmt *sql, OCIBind **hbnd, OCIError *err, in
 }
 
 int QOCIResultPrivate::bindValues(QVector<QVariant> &values, IndicatorArray &indicators,
-                            SizeArray &tmpSizes, QList<QByteArray> &tmpStorage)
+                                  SizeArray &tmpSizes, TempStorage &tmpStorage)
 {
     int r = OCI_SUCCESS;
     for (int i = 0; i < values.count(); ++i) {
@@ -465,27 +518,30 @@ int QOCIResultPrivate::bindValues(QVector<QVariant> &values, IndicatorArray &ind
 }
 
 // will assign out value and remove its temp storage.
-static void qOraOutValue(QVariant &value, QList<QByteArray> &storage, OCIError* err)
+static void qOraOutValue(QVariant &value, TempStorage &tmpStorage, OCIEnv *env, OCIError* err)
 {
     switch (value.type()) {
     case QVariant::Time:
-        value = qMakeDate(storage.takeFirst()).time();
+        value = QOCIDateTime::fromOCIDateTime(env, err,
+                                              tmpStorage.dateTimes.takeFirst()->dateTime).time();
         break;
     case QVariant::Date:
-        value = qMakeDate(storage.takeFirst()).date();
+        value = QOCIDateTime::fromOCIDateTime(env, err,
+                                              tmpStorage.dateTimes.takeFirst()->dateTime).date();
         break;
     case QVariant::DateTime:
-        value = qMakeDate(storage.takeFirst());
+        value = QOCIDateTime::fromOCIDateTime(env, err,
+                                              tmpStorage.dateTimes.takeFirst()->dateTime);
         break;
     case QVariant::LongLong:
-        value = qMakeLongLong(storage.takeFirst(), err);
+        value = qMakeLongLong(tmpStorage.rawData.takeFirst(), err);
         break;
     case QVariant::ULongLong:
-        value = qMakeULongLong(storage.takeFirst(), err);
+        value = qMakeULongLong(tmpStorage.rawData.takeFirst(), err);
         break;
     case QVariant::String:
         value = QString(
-                reinterpret_cast<const QChar *>(storage.takeFirst().constData()));
+                reinterpret_cast<const QChar *>(tmpStorage.rawData.takeFirst().constData()));
         break;
     default:
         break; //nothing
@@ -493,14 +549,14 @@ static void qOraOutValue(QVariant &value, QList<QByteArray> &storage, OCIError*
 }
 
 void QOCIResultPrivate::outValues(QVector<QVariant> &values, IndicatorArray &indicators,
-                            QList<QByteArray> &tmpStorage)
+                                  TempStorage &tmpStorage)
 {
     for (int i = 0; i < values.count(); ++i) {
 
         if (!isOutValue(i))
             continue;
 
-        qOraOutValue(values[i], tmpStorage, err);
+        qOraOutValue(values[i], tmpStorage, env, err);
 
         QVariant::Type typ = values.at(i).type();
         if (indicators[i] == -1) // NULL
@@ -692,11 +748,9 @@ QVariant::Type qDecodeOCIType(int ocitype, QSql::NumericalPrecisionPolicy precis
         break;
     case SQLT_DAT:
     case SQLT_ODT:
-#ifdef SQLT_TIMESTAMP
     case SQLT_TIMESTAMP:
     case SQLT_TIMESTAMP_TZ:
     case SQLT_TIMESTAMP_LTZ:
-#endif
         type = QVariant::DateTime;
         break;
     default:
@@ -723,27 +777,6 @@ static QSqlField qFromOraInf(const OraFieldInfo &ofi)
 }
 
 /*!
-    \internal
-
-    Convert QDateTime to the internal Oracle DATE format NB!
-    It does not handle BCE dates.
-*/
-QByteArray qMakeOraDate(const QDateTime& dt)
-{
-    QByteArray ba;
-    ba.resize(7);
-    int year = dt.date().year();
-    ba[0]= (year / 100) + 100; // century
-    ba[1]= (year % 100) + 100; // year
-    ba[2]= dt.date().month();
-    ba[3]= dt.date().day();
-    ba[4]= dt.time().hour() + 1;
-    ba[5]= dt.time().minute() + 1;
-    ba[6]= dt.time().second() + 1;
-    return ba;
-}
-
-/*!
   \internal
 
    Convert qlonglong to the internal Oracle OCINumber format.
@@ -793,22 +826,6 @@ qulonglong qMakeULongLong(const char* ociNumber, OCIError* err)
     return qull;
 }
 
-QDateTime qMakeDate(const char* oraDate)
-{
-    int century = uchar(oraDate[0]);
-    if(century >= 100){
-        int year    = uchar(oraDate[1]);
-        year = ((century-100)*100) + (year-100);
-        int month = oraDate[2];
-        int day   = oraDate[3];
-        int hour  = oraDate[4] - 1;
-        int min   = oraDate[5] - 1;
-        int sec   = oraDate[6] - 1;
-        return QDateTime(QDate(year,month,day), QTime(hour,min,sec));
-    }
-    return QDateTime();
-}
-
 class QOCICols
 {
 public:
@@ -831,7 +848,7 @@ private:
     class OraFieldInf
     {
     public:
-        OraFieldInf(): data(0), len(0), ind(0), typ(QVariant::Invalid), oraType(0), def(0), lob(0)
+        OraFieldInf() : data(0), len(0), ind(0), typ(QVariant::Invalid), oraType(0), def(0), lob(0), dataPtr(nullptr)
         {}
         ~OraFieldInf();
         char *data;
@@ -841,6 +858,7 @@ private:
         ub4 oraType;
         OCIDefine *def;
         OCILobLocator *lob;
+        void *dataPtr;
     };
 
     QVector<OraFieldInf> fieldInf;
@@ -855,6 +873,20 @@ QOCICols::OraFieldInf::~OraFieldInf()
         if (r != 0)
             qWarning("QOCICols: Cannot free LOB descriptor");
     }
+    if (dataPtr) {
+        switch (typ) {
+        case QVariant::Date:
+        case QVariant::Time:
+        case QVariant::DateTime: {
+            int r = OCIDescriptorFree(dataPtr, OCI_DTYPE_TIMESTAMP_TZ);
+            if (r != OCI_SUCCESS)
+                qWarning("QOCICols: Cannot free OCIDateTime descriptor");
+            break;
+        }
+        default:
+            break;
+        }
+    }
 }
 
 QOCICols::QOCICols(int size, QOCIResultPrivate* dp)
@@ -901,13 +933,18 @@ QOCICols::QOCICols(int size, QOCIResultPrivate* dp)
 
         switch (ofi.type) {
         case QVariant::DateTime:
+            r = OCIDescriptorAlloc(d->env, (void **)&fieldInf[idx].dataPtr, OCI_DTYPE_TIMESTAMP_TZ, 0, 0);
+            if (r != OCI_SUCCESS) {
+                qWarning("QOCICols: Unable to allocate the OCIDateTime descriptor");
+                break;
+            }
             r = OCIDefineByPos(d->sql,
                                &dfn,
                                d->err,
                                count,
-                               create(idx, dataSize+1),
-                               dataSize+1,
-                               SQLT_DAT,
+                               &fieldInf[idx].dataPtr,
+                               sizeof(OCIDateTime *),
+                               SQLT_TIMESTAMP_TZ,
                                &(fieldInf[idx].ind),
                                0, 0, OCI_DEFAULT);
             break;
@@ -1322,11 +1359,10 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
         fieldTypes.append(tp == QVariant::List ? boundValues.at(i).toList().value(0).type()
                                                : tp);
     }
-
-    QList<QByteArray> tmpStorage;
     SizeArray tmpSizes(columnCount);
     QVector<QOCIBatchColumn> columns(columnCount);
     QOCIBatchCleanupHandler cleaner(columns);
+    TempStorage tmpStorage;
 
     // figuring out buffer sizes
     for (i = 0; i < columnCount; ++i) {
@@ -1363,8 +1399,8 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
             case QVariant::Time:
             case QVariant::Date:
             case QVariant::DateTime:
-                col.bindAs = SQLT_DAT;
-                col.maxLen = 7;
+                col.bindAs = SQLT_TIMESTAMP_TZ;
+                col.maxLen = sizeof(OCIDateTime *);
                 break;
 
             case QVariant::Int:
@@ -1432,7 +1468,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
         for (uint row = 0; row < col.recordCount; ++row) {
             const QVariant &val = boundValues.at(i).toList().at(row);
 
-            if (val.isNull()){
+            if (val.isNull() && !d->isOutValue(i)) {
                 columns[i].indicators[row] = -1;
                 columns[i].lengths[row] = 0;
             } else {
@@ -1443,9 +1479,8 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
                     case QVariant::Date:
                     case QVariant::DateTime:{
                         columns[i].lengths[row] = columns[i].maxLen;
-                        const QByteArray ba = qMakeOraDate(val.toDateTime());
-                        Q_ASSERT(ba.size() == int(columns[i].maxLen));
-                        memcpy(dataPtr, ba.constData(), columns[i].maxLen);
+                        QOCIDateTime *date = new QOCIDateTime(d->env, d->err, val.toDateTime());
+                        *reinterpret_cast<OCIDateTime**>(dataPtr) = date->dateTime;
                         break;
                     }
                     case QVariant::Int:
@@ -1581,7 +1616,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
 
         QVariant::Type tp = boundValues.at(i).type();
         if (tp != QVariant::List) {
-            qOraOutValue(boundValues[i], tmpStorage, d->err);
+            qOraOutValue(boundValues[i], tmpStorage, d->env, d->err);
             if (*columns[i].indicators == -1)
                 boundValues[i] = QVariant(tp);
             continue;
@@ -1593,16 +1628,16 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
         for (uint r = 0; r < columns[i].recordCount; ++r){
 
             if (columns[i].indicators[r] == -1) {
-                (*list)[r] = QVariant();
+                (*list)[r] = QVariant(fieldTypes[i]);
                 continue;
             }
 
             switch(columns[i].bindAs) {
 
-                case SQLT_DAT:
-                    (*list)[r] =  qMakeDate(data + r * columns[i].maxLen);
+                case SQLT_TIMESTAMP_TZ:
+                    (*list)[r] = QOCIDateTime::fromOCIDateTime(d->env, d->err,
+                                    *reinterpret_cast<OCIDateTime **>(data + r * columns[i].maxLen));
                     break;
-
                 case SQLT_INT:
                     (*list)[r] =  *reinterpret_cast<int*>(data + r * columns[i].maxLen);
                     break;
@@ -1646,6 +1681,7 @@ bool QOCICols::execBatch(QOCIResultPrivate *d, QVector<QVariant> &boundValues, b
     d->q_func()->setAt(QSql::BeforeFirstRow);
     d->q_func()->setActive(true);
 
+    qDeleteAll(tmpStorage.dateTimes);
     return true;
 }
 
@@ -1754,7 +1790,8 @@ void QOCICols::getValues(QVector<QVariant> &v, int index)
 
         switch (fld.typ) {
         case QVariant::DateTime:
-            v[index + i] = QVariant(qMakeDate(fld.data));
+            v[index + i] = QVariant(QOCIDateTime::fromOCIDateTime(d->env, d->err,
+                                        reinterpret_cast<OCIDateTime *>(fld.dataPtr)));
             break;
         case QVariant::Double:
         case QVariant::Int:
@@ -1984,7 +2021,7 @@ bool QOCIResult::exec()
     ub2 stmtType=0;
     ub4 iters;
     ub4 mode;
-    QList<QByteArray> tmpStorage;
+    TempStorage tmpStorage;
     IndicatorArray indicators(boundValueCount());
     SizeArray tmpSizes(boundValueCount());
 
@@ -2055,7 +2092,7 @@ bool QOCIResult::exec()
 
     if (hasOutValues())
         d->outValues(boundValues(), indicators, tmpStorage);
-
+    qDeleteAll(tmpStorage.dateTimes);
     return true;
 }
 
diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 92e4b580e7..2a27f24314 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 QTBUG_57138_data() { generic_data("QSQLITE"); }
     void QTBUG_57138();
 
+    void dateTime_data();
+    void dateTime();
+
 private:
     // returns all database connections
     void generic_data(const QString &engine=QString());
@@ -724,6 +727,30 @@ void tst_QSqlQuery::oraOutValues()
     q.addBindValue( out, QSql::Out );
     QVERIFY_SQL( q, exec() );
     QCOMPARE( q.boundValue( 1 ).toString(), QString( "fifibubulalakikikokololo" ) );
+
+    /*** in/outvalue date ***/
+    QVERIFY_SQL(q, exec("create or replace procedure " + tst_outValues + "(x in date, y out date) is\n"
+        "begin\n"
+        "    y := x;\n"
+        "end;\n"));
+    QVERIFY(q.prepare("call " + tst_outValues + "(?, ?)"));
+    const QDate date = QDate::currentDate();
+    q.addBindValue(date, QSql::In);
+    q.addBindValue(QVariant(QDate()), QSql::Out);
+    QVERIFY_SQL(q, exec());
+    QCOMPARE(q.boundValue(1).toDate(), date);
+
+    /*** in/outvalue timestamp ***/
+    QVERIFY_SQL(q, exec("create or replace procedure " + tst_outValues + "(x in timestamp, y out timestamp) is\n"
+        "begin\n"
+        "    y := x;\n"
+        "end;\n"));
+    QVERIFY(q.prepare("call " + tst_outValues + "(?, ?)"));
+    const QDateTime dt = QDateTime::currentDateTime();
+    q.addBindValue(dt, QSql::In);
+    q.addBindValue(QVariant(QVariant::DateTime), QSql::Out);
+    QVERIFY_SQL(q, exec());
+    QCOMPARE(q.boundValue(1).toDateTime(), dt);
 }
 
 void tst_QSqlQuery::oraClob()
@@ -2138,8 +2165,8 @@ void tst_QSqlQuery::batchExec()
     QSqlQuery q( db );
     const QString tableName = qTableName("qtest_batch", __FILE__, db);
 
-    QVERIFY_SQL( q, exec( "create table " + tableName + " (id int, name varchar(20), dt date, num numeric(8, 4))" ) );
-    QVERIFY_SQL( q, prepare( "insert into " + tableName + " (id, name, dt, num) values (?, ?, ?, ?)" ) );
+    QVERIFY_SQL(q, exec("create table " + tableName + " (id int, name varchar(20), dt date, num numeric(8, 4), dtstamp TIMESTAMP(3))"));
+    QVERIFY_SQL(q, prepare("insert into " + tableName + " (id, name, dt, num, dtstamp) values (?, ?, ?, ?, ?)"));
 
     QVariantList intCol;
     intCol << 1 << 2 << QVariant( QVariant::Int );
@@ -2154,31 +2181,54 @@ void tst_QSqlQuery::batchExec()
     QVariantList numCol;
     numCol << 2.3 << 3.4 << QVariant( QVariant::Double );
 
-    q.addBindValue( intCol );
+    QVariantList timeStampCol;
+    const QDateTime dtStamp = QDateTime(QDate::currentDate(), QTime(1, 2, 3, 4));
+    timeStampCol << dtStamp << dtStamp.addDays(-1) << QVariant(QVariant::DateTime);
+
+    q.addBindValue(intCol);
     q.addBindValue( charCol );
     q.addBindValue( dateCol );
     q.addBindValue( numCol );
+    q.addBindValue(timeStampCol);
 
     QVERIFY_SQL( q, execBatch() );
-    QVERIFY_SQL( q, exec( "select id, name, dt, num from " + tableName + " order by id" ) );
+    QVERIFY_SQL(q, exec("select id, name, dt, num, dtstamp from " + tableName + " order by id"));
 
     QVERIFY( q.next() );
     QCOMPARE( q.value( 0 ).toInt(), 1 );
     QCOMPARE( q.value( 1 ).toString(), QString( "harald" ) );
     QCOMPARE( q.value( 2 ).toDateTime(), dt );
     QCOMPARE( q.value( 3 ).toDouble(), 2.3 );
+    QCOMPARE(q.value(4).toDateTime(), dtStamp);
 
     QVERIFY( q.next() );
     QCOMPARE( q.value( 0 ).toInt(), 2 );
     QCOMPARE( q.value( 1 ).toString(), QString( "boris" ) );
     QCOMPARE( q.value( 2 ).toDateTime(), dt.addDays( -1 ) );
     QCOMPARE( q.value( 3 ).toDouble(), 3.4 );
+    QCOMPARE(q.value(4).toDateTime(), dtStamp.addDays(-1));
 
     QVERIFY( q.next() );
     QVERIFY( q.value( 0 ).isNull() );
     QVERIFY( q.value( 1 ).isNull() );
     QVERIFY( q.value( 2 ).isNull() );
     QVERIFY( q.value( 3 ).isNull() );
+    QVERIFY(q.value(4).isNull());
+
+    const QString procName = qTableName("qtest_batch_proc", __FILE__, db);
+    QVERIFY_SQL(q, exec("create or replace procedure " + procName + " (x in timestamp, y out timestamp) is\n"
+        "begin\n"
+        "    y := x;\n"
+        "end;\n"));
+    QVERIFY(q.prepare("call " + procName + "(?, ?)"));
+    q.addBindValue(timeStampCol, QSql::In);
+    QVariantList emptyDateTimes;
+    emptyDateTimes.reserve(timeStampCol.size());
+    for (int i = 0; i < timeStampCol.size(); i++)
+        emptyDateTimes << QVariant(QDateTime());
+    q.addBindValue(emptyDateTimes, QSql::Out);
+    QVERIFY_SQL(q, execBatch());
+    QCOMPARE(q.boundValue(1).toList(), timeStampCol);
 }
 
 void tst_QSqlQuery::QTBUG_43874()
@@ -2894,16 +2944,14 @@ void tst_QSqlQuery::timeStampParsing()
     QFETCH(QString, dbName);
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
+    const QString tableName(qTableName("timeStampParsing", __FILE__, db));
+    tst_Databases::safeDropTable(db, tableName);
     QSqlQuery q(db);
-    QVERIFY_SQL(q, exec(
-                    "CREATE TABLE \"main\".\"datetest\" ("
-                    "\"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "\"datefield\" timestamp);"
-                    ));
-    QVERIFY_SQL(q, exec(
-                    "INSERT INTO datetest (datefield) VALUES (current_timestamp);"
-                    ));
-    QVERIFY_SQL(q, exec("SELECT * FROM datetest;"));
+    QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName
+                        + QStringLiteral(" (id integer, datefield timestamp)")));
+    QVERIFY_SQL(q, exec(QStringLiteral("INSERT INTO ") + tableName
+                        + QStringLiteral(" (datefield) VALUES (current_timestamp)")));
+    QVERIFY_SQL(q, exec(QStringLiteral("SELECT * FROM ") + tableName));
     while (q.next())
         QVERIFY(q.value(1).toDateTime().isValid());
 }
@@ -4142,5 +4190,88 @@ void tst_QSqlQuery::QTBUG_57138()
     QCOMPARE(q.value(2).toDateTime(), tzoffset);
 }
 
+void tst_QSqlQuery::dateTime_data()
+{
+    QTest::addColumn<QString>("dbName");
+    QTest::addColumn<QString>("tableName");
+    QTest::addColumn<QString>("createTableString");
+    QTest::addColumn<QList<QDateTime> >("initialDateTimes");
+    QTest::addColumn<QList<QDateTime> >("expectedDateTimes");
+
+    // Using time zones which are highly unlikely to be the same as the testing machine's one
+    // as it could pass as a result despite it.
+    // +8.5 hours from UTC to North Korea
+    const QTimeZone afterUTCTimeZone(30600);
+    // -8 hours from UTC to Belize
+    const QTimeZone beforeUTCTimeZone(-28800);
+    const QTimeZone utcTimeZone("UTC");
+    const QDateTime dt(QDate(2015, 5, 18), QTime(4, 26, 30));
+    const QDateTime dtWithMS(QDate(2015, 5, 18), QTime(4, 26, 30, 500));
+    const QDateTime dtWithAfterTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), afterUTCTimeZone);
+    const QDateTime dtWithBeforeTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), beforeUTCTimeZone);
+    const QDateTime dtWithUTCTZ(QDate(2015, 5, 18), QTime(4, 26, 30, 500), utcTimeZone);
+    const QList<QDateTime> dateTimes = { dt, dtWithMS, dtWithAfterTZ, dtWithBeforeTZ, dtWithUTCTZ };
+    const QList<QDateTime> expectedDateTimesLocalTZ = { dt, dtWithMS, dtWithAfterTZ.toLocalTime(),
+                                                        dtWithBeforeTZ.toLocalTime(),
+                                                        dtWithUTCTZ.toLocalTime() };
+    const QList<QDateTime> expectedTimeStampDateTimes = { dt, dtWithMS, dtWithMS, dtWithMS, dtWithMS };
+    const QList<QDateTime> expectedDateTimes = { dt, dt, dt, dt, dt };
+
+    for (const QString &dbName : qAsConst(dbs.dbNames)) {
+        QSqlDatabase db = QSqlDatabase::database(dbName);
+        if (!db.isValid())
+            continue;
+        const QString tableNameTSWithTimeZone(qTableName("dateTimeTSWithTimeZone", __FILE__, db));
+        const QString tableNameTSWithLocalTimeZone(qTableName("dateTimeTSWithLocalTimeZone", __FILE__, db));
+        const QString tableNameTS(qTableName("dateTimeTS", __FILE__, db));
+        const QString tableNameDate(qTableName("dateTimeDate", __FILE__, db));
+        QTest::newRow(QString(dbName + " timestamp with time zone").toLatin1())
+                        << dbName << tableNameTSWithTimeZone
+                        << QStringLiteral(" (dt TIMESTAMP WITH TIME ZONE)")
+                        << dateTimes << dateTimes;
+        QTest::newRow(QString(dbName + " timestamp with local time zone").toLatin1())
+                        << dbName << tableNameTSWithTimeZone
+                        << QStringLiteral(" (dt TIMESTAMP WITH LOCAL TIME ZONE)")
+                        << dateTimes << expectedDateTimesLocalTZ;
+        QTest::newRow(QString(dbName + "timestamp").toLatin1())
+                        << dbName << tableNameTS << QStringLiteral(" (dt TIMESTAMP(3))")
+                        << dateTimes << expectedTimeStampDateTimes;
+        QTest::newRow(QString(dbName + "date").toLatin1())
+                        << dbName << tableNameDate << QStringLiteral(" (dt DATE)")
+                        << dateTimes << expectedDateTimes;
+    }
+}
+
+void tst_QSqlQuery::dateTime()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+
+    QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+    if (dbType != QSqlDriver::Oracle)
+        QSKIP("Implemented only for Oracle");
+
+    QFETCH(QString, tableName);
+    QFETCH(QString, createTableString);
+    QFETCH(QList<QDateTime>, initialDateTimes);
+    QFETCH(QList<QDateTime>, expectedDateTimes);
+
+    tst_Databases::safeDropTable(db, tableName);
+
+    QSqlQuery q(db);
+    QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + createTableString));
+    for (const QDateTime &dt : qAsConst(initialDateTimes)) {
+        QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " values(:dt)"));
+        q.bindValue(":dt", dt);
+        QVERIFY_SQL(q, exec());
+    }
+    QVERIFY_SQL(q, exec("SELECT * FROM " + tableName));
+    for (const QDateTime &dt : qAsConst(expectedDateTimes)) {
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDateTime(), dt);
+    }
+}
+
 QTEST_MAIN( tst_QSqlQuery )
 #include "tst_qsqlquery.moc"