3

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?

Di Zou
  • 4,469
  • 13
  • 59
  • 88
  • Have you tried getting the value as a QString (`value(0).toString()`)? What does that return? – Mat Nov 22 '11 at 16:01
  • It returns something like this: "2011-11-21T12:25:56" – Di Zou Nov 22 '11 at 16:15
  • I fear you'll have to implement a workaround then (convert to a string format in the query, do a manual QString -> QDateTime conversion in Qt with a custom format), but I don't know for sure... – Mat Nov 22 '11 at 16:17
  • That's what I did originally, but there is no millisecond in the QString either though. – Di Zou Nov 22 '11 at 16:21
  • Yes, that's why I think you'll need to make the data type your query (SQL) returns a string-type and not a date-type. – Mat Nov 22 '11 at 16:22

1 Answers1

2

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

user3159253
  • 16,836
  • 3
  • 30
  • 56
pnezis
  • 12,023
  • 2
  • 38
  • 38
  • 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" – Di Zou Nov 22 '11 at 20:33
  • Try to change your query to : "SELECT CONVERT(LOG_TIME, CHAR(31)) from PCY_LOG_EVENTS" – pnezis Nov 22 '11 at 20:47
  • I get this error when I try that: ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 1 Column: 25 – Di Zou Nov 22 '11 at 21:54
  • Did you have a work around of this? I guess I have the same issue here http://stackoverflow.com/questions/31582537/mysql-timestamp-to-qdatetime-with-miliseconds – KcFnMi Jul 24 '15 at 09:14
  • @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! – Di Zou Jul 29 '15 at 19:55