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.

Answer 2

From MySQL 5.1 documentation:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns.

It seems like seconds is the best you can do with timestamp.