Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

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 (like 20210113214615.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 a QString overload for that. Again, we may have rounding at the double 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 :)


  • Lifetime Qt Champion

    @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?


  • Lifetime Qt Champion

    @JonB said in QSql rounding timestamp:

    So that is... microseconds

    I would guess so :)



  • @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


Log in to reply