qt - MySQL TIMESTAMP to QDateTime with milliseconds

Question

If I use a QSqlTableModel to access a MySQL database I can convert a TIMESTAMP field using the following:

QDateTime dateTime = index(section, column).data().toDateTime();
QString str = dateTime.toString("yyyy-MM-dd hh:mm:ss.zzz");

So str shows, i.e. 2014-06-22 22:11:44.221. But I want to access the database using QSqlQuerry, so I do:

QDateTime dateTime = query.value(column).toDateTime();
str = dateTime.toString("yyyy-MM-dd hh:mm:ss.zzz");

But now I'm missing the milliseconds, str shows 2014-06-22 22:11:44.000. What's the proper way to see the milliseconds?

If I do str = query.value(column).toString(); then I get 2014-06-22T22:11:44.

Answer 1

From this page:

https://dev.mysql.com/doc/refman/5.6/en/datetime.html

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, as of MySQL 5.6.4, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded.

So, the millisecond is there in MySQL! But the query.value() does not get it - at this point in the Qt history as pointed by @peppe here.

Relating back to the original question: There is no proper way to see the millisecond since the query does not have it. One alternative could be to modify the query, from:

SELECT timestamp FROM table;

to

SELECT DATE_FORMAT(timestamp, '%Y-%c-%e %H:%i:%s.%f') as timestamp FROM table;

And then finish the job with:

QString str = query.value(column).toString();
QDateTime dateTime = QDateTime::fromString(str, "yyyy-MM-dd hh:mm:ss.zzz000");

I got the insight from here.