MySQL TIMESTAMP到QDateTime的毫秒 [英] MySQL TIMESTAMP to QDateTime with milliseconds

问题描述

如果我使用QSqlTableModel访问MySQL数据库，则可以使用以下内容转换TIMESTAMP字段:

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");

因此显示str，即2014-06-22 22:11:44.221. 但是我想使用QSqlQuerry访问数据库，所以我这样做:

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");

但是现在我错过了毫秒，str显示2014-06-22 22:11:44.000.看毫秒的正确方法是什么?

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?

如果我执行str = query.value(column).toString();，则得到2014-06-22T22:11:44.

推荐答案

在此页面上:

https://dev.mysql.com/doc/refman /5.6/zh-CN/datetime.html

DATETIME或TIMESTAMP值可以包含尾随小数 秒部分的精度最高为微秒(6位数).在 特别是从MySQL 5.6.4开始，插入值中的任何小数部分 放入DATETIME或TIMESTAMP列中，是存储，而不是丢弃.

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.

因此，MySQL中存在毫秒！但是query.value()无法获得它-在 @peppe

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;

到

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");

我从 查看全文