Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

qt - MySQL TIMESTAMP to QDateTime with milliseconds

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...