13

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.

KcFnMi
  • 5,516
  • 10
  • 62
  • 136
  • Are you sure that TIMESTAMP has nanoseconds? – Miki Jul 23 '15 at 09:36
  • Sure. Besides seeing the miliseconds while accessing with QTableModel, I can confirm it accessing the database with HeidiSQL (www.heidisql.com). – KcFnMi Jul 23 '15 at 10:09
  • HeidiSQL actually says the field is a TIMESTAMP(3). – KcFnMi Jul 23 '15 at 10:15
  • take a look [here](http://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql), probably it will help you – Miki Jul 23 '15 at 10:15
  • Unfortunately I did not understand (It appers to me that question is more about MySQL, nothing about Qt). I've edited the question. Is there any chance that the query above cannot retrieve the miliseconds? – KcFnMi Jul 24 '15 at 08:55
  • Looks like the issue here is the same as (without response) http://stackoverflow.com/questions/8229658/how-do-i-get-milliseconds-in-a-qdatetime-with-qsqlquery-in-qt-c – KcFnMi Jul 24 '15 at 09:18

2 Answers2

8

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.

Community
  • 1
  • 1
KcFnMi
  • 5,516
  • 10
  • 62
  • 136
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.

Chnossos
  • 9,971
  • 4
  • 28
  • 40
Michael Vincent
  • 1,620
  • 1
  • 20
  • 46
  • I checked the script which creates the database, the field is a TIMESTAMP(3) - MySQL 5.6.17. Definitely it has miliseconds. Do you agree? Also, I've checked how the INSERT is made, it's a QDateTime::toString with the same format as said in the question. At this point I'm in doubt if this approach is correct (the best), I mean: Is TIMESTAMP(3) the best type to store a QDateTime::toString("yyyy-MM-dd hh:mm:ss.zzz")? Anyway now I think I put the question to much in the MySQL side, my intent is to work in the Qt side (how to make the miliseconds appear in that query above?). – KcFnMi Jul 24 '15 at 08:34