Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSql rounding timestamp
Forum Updated to NodeBB v4.3 + New Features

QSql rounding timestamp

Scheduled Pinned Locked Moved Solved General and Desktop
9 Posts 4 Posters 656 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • H Offline
    H Offline
    HTWAMD
    wrote on 15 Jan 2021, 11:42 last edited by
    #1

    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?

    J 1 Reply Last reply 15 Jan 2021, 12:34
    0
    • H HTWAMD
      15 Jan 2021, 11:42

      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?

      J Offline
      J Offline
      JonB
      wrote on 15 Jan 2021, 12:34 last edited by JonB
      #2

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

      1 Reply Last reply
      0
      • H Offline
        H Offline
        HTWAMD
        wrote on 15 Jan 2021, 12:56 last edited by
        #3

        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"

        J 1 Reply Last reply 15 Jan 2021, 13:20
        0
        • H HTWAMD
          15 Jan 2021, 12:56

          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"

          J Offline
          J Offline
          JonB
          wrote on 15 Jan 2021, 13:20 last edited by JonB
          #4

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

          C 1 Reply Last reply 15 Jan 2021, 18:03
          2
          • H Offline
            H Offline
            HTWAMD
            wrote on 15 Jan 2021, 13:32 last edited by HTWAMD
            #5

            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 :)

            1 Reply Last reply
            2
            • J JonB
              15 Jan 2021, 13:20

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

              C Offline
              C Offline
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on 15 Jan 2021, 18:03 last edited by
              #6

              @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 :)

              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
              Visit the Qt Academy at https://academy.qt.io/catalog

              J 1 Reply Last reply 15 Jan 2021, 18:22
              0
              • C Christian Ehrlicher
                15 Jan 2021, 18:03

                @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 :)

                J Offline
                J Offline
                JonB
                wrote on 15 Jan 2021, 18:22 last edited by JonB
                #7

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

                C A 2 Replies Last reply 15 Jan 2021, 18:24
                0
                • J JonB
                  15 Jan 2021, 18:22

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

                  C Offline
                  C Offline
                  Christian Ehrlicher
                  Lifetime Qt Champion
                  wrote on 15 Jan 2021, 18:24 last edited by
                  #8

                  @JonB said in QSql rounding timestamp:

                  So that is... microseconds

                  I would guess so :)

                  Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                  Visit the Qt Academy at https://academy.qt.io/catalog

                  1 Reply Last reply
                  0
                  • J JonB
                    15 Jan 2021, 18:22

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

                    A Offline
                    A Offline
                    artwaw
                    wrote on 15 Jan 2021, 19:11 last edited by
                    #9

                    @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

                    For more information please re-read.

                    Kind Regards,
                    Artur

                    1 Reply Last reply
                    0

                    1/9

                    15 Jan 2021, 11:42

                    • Login

                    • Login or register to search.
                    1 out of 9
                    • First post
                      1/9
                      Last post
                    0
                    • Categories
                    • Recent
                    • Tags
                    • Popular
                    • Users
                    • Groups
                    • Search
                    • Get Qt Extensions
                    • Unsolved