How do I get milliseconds in a QDateTime with QSqlQuery in Qt C++?

Question

I have this sql query:

SELECT LOG_TIME FROM PCY_LOG_EVENTS;

This returns data in the format "DD-MMM-YY HH.MM.SS.MS" like this:

30-OCT-11 09.00.57.638000000 AM

In my Qt code, I have this:

QSqlQuery query("SELECT LOG_TIME from PCY_LOG_EVENTS", db);

while(query.next()) {
    //Displays a QMessageBox with the millisecond part of the QDateTime
    this->messageBox(QString::number(query.value(0).toDateTime().time().msec()));
}

I get 0 for all the millisecond values. Is there a reason why the millisecond values are not being stored? How would I get the millisecond values?

Solution

Get the query value as a QString

QString dateTimeString = query.value(0).toString();

Then use the static fromString function of the QDateTime. You have to specify the format of your string. I assume the days of the month have a leading zero

QDateTime dateTime = QDateTime::fromString(dateTimeString, "dd-MMM-yy hh.mm.ss.zzz000000 A")

Notice the milliseconds part :zzz000000. Since the max value can be 999 the trailing zeros of your example make no sense. So by using the zzz followed by the zeros you can get the miliseconds stored in your string. The only possible problem is that your month part uses upper case letters while the MMM returns the month abbreviation with just the first letter capitalized. I hope there won't be a problem with it.

Once you do the conversion you can easily get the milliseconds:

int ms = dateTime.time().msec();

For more formatting options here

Comments

[Di Zou]

I just tried this and this didn't work. Like I said above, query.value(0).toString() gives you something like this: "2011-11-21T12:25:56"

[pnezis]

Try to change your query to : "SELECT CONVERT(LOG_TIME, CHAR(31)) from PCY_LOG_EVENTS"

[Di Zou]

I get this error when I try that: ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 1 Column: 25

[KcFnMi]

Did you have a work around of this? I guess I have the same issue here stackoverflow.com/questions/31582537/…

[Di Zou]

@KcFnMi Sorry, this was years ago and I don't remember what I did to work around it. I saw that in your question you were able to find a solution though!