Solved QSql rounding timestamp
-
Hey guys,
I'm using Qt + MySql to get some database data. I have a huge problem with a NOW(6) timestamp.
Inside the database the value is for example "2021-01-13 21:46:15.072880". After I select the value and convert with toString(), the value is rounded to the this: "2021-01-13 21:46:15.073" (see the last digits).How can I get the correct value from the database?
-
@HTWAMD
It is unclear to me where such rounding may be happening. I would try debugging a couple of things:-
Can you debug out what is really in the variant returned, without going
toString()
? -
Can you try seeing what you get if you make your SQL expression be
NOW(6) + 0
? This will be a floating point number (like20210113214615.072880
), I want to see what the fractional part is.
-
-
Without toString() I get this debug output:
QVariant(QDateTime, QDateTime(2021-01-13 21:46:15.073 Mitteleuropäische Zeit Qt::LocalTime))
So it's already converted to a rounded QDateTime.Adding a +0 to a NOW(6), convert it's to a double value:
QVariant(double, 2.02101e+13)
Here also with calling toString():
"20210113214615.074" -
@HTWAMD
I'm only guessing with you here. Thankfully you seem to know what you're doing better than many here :) So I don't have to spell everything out.I'm not even sure what the precision of a
QDateTime
. The docs don't say.When you printed out the double case with
toString()
, can you go check to use the one which shows 6 digits after the decimal place for sure, I think there's aQString
overload for that. Again, we may have rounding at thedouble
level.I think you should verify what
NOW(6) AS VARCHAR(40)
, or whatever it is in MySQL to make it a string with all its precision, returns. Let's make sure we really do get those 6 decimal places. It may be that you'll have to do it like this, and do whatever work on it in the client, because I don't know where the rounding is currently happening. -
Thanks, use NOW(6) as a char/varchar is a good idea!
I'm just casting the value in the select query like that: CAST(timestamp AS char)Now I can use toString() and I get the exact value. Thanks a lot :)
-
@JonB said in QSql rounding timestamp:
The docs don't say.
But since there is only msec and not nsec or similar, it's msec :)
-
@Christian-Ehrlicher
Oohh, that's why it's 3 digits after the decimal place!So, out of interest: the OP is getting MySQL to return
NOW(6)
to 6 decimal places. So that is... microseconds? Which I suppose is reasonable. Not nano seconds. They come next? Do PCs do anything at nanosecond speed? -
-
@JonB Thats microseconds (10^−6). And it's maximum resolution of time value for MySQL: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html