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. QT 6.8.0: Comparison of QDateTime is wrong
QtWS25 Last Chance

QT 6.8.0: Comparison of QDateTime is wrong

Scheduled Pinned Locked Moved Solved General and Desktop
qt 6.8.0c++qdatetime
19 Posts 4 Posters 1.3k Views
  • 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.
  • T Offline
    T Offline
    TheoSys
    wrote on 4 Nov 2024, 10:03 last edited by
    #1

    I retrieve a timestamp from a PostgreSQL database. The timestamp is in my case one that indicates that there is no valid timestamp available. It's 1900-01-01T00:00:00Z. The if in the following code is true, but why?

    QJsonObject event;
    ...
    QDateTime evId = query.value(23).toDateTime();              // The timestamp from the database (1900-01-01T00:00:00Z)
    QDateTime invalidDate(QDate(1900, 1, 1), QTime(0, 0, 0));   // Reference date which marks an invalid timestamp
    
    if (evId > invalidDate)    // TRUE! Why??
        event.insert("ev_id", evId.toString(Qt::ISODate));
    

    Why is the expression of the if in the above code true?

    A.T.

    J J 2 Replies Last reply 4 Nov 2024, 10:07
    0
    • T TheoSys
      4 Nov 2024, 10:03

      I retrieve a timestamp from a PostgreSQL database. The timestamp is in my case one that indicates that there is no valid timestamp available. It's 1900-01-01T00:00:00Z. The if in the following code is true, but why?

      QJsonObject event;
      ...
      QDateTime evId = query.value(23).toDateTime();              // The timestamp from the database (1900-01-01T00:00:00Z)
      QDateTime invalidDate(QDate(1900, 1, 1), QTime(0, 0, 0));   // Reference date which marks an invalid timestamp
      
      if (evId > invalidDate)    // TRUE! Why??
          event.insert("ev_id", evId.toString(Qt::ISODate));
      

      Why is the expression of the if in the above code true?

      A.T.

      J Offline
      J Offline
      jsulm
      Lifetime Qt Champion
      wrote on 4 Nov 2024, 10:07 last edited by
      #2

      @TheoSys Print out both datetimes to see what they really are.
      Also timezone could play a role here I guess.

      https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      2
      • T TheoSys
        4 Nov 2024, 10:03

        I retrieve a timestamp from a PostgreSQL database. The timestamp is in my case one that indicates that there is no valid timestamp available. It's 1900-01-01T00:00:00Z. The if in the following code is true, but why?

        QJsonObject event;
        ...
        QDateTime evId = query.value(23).toDateTime();              // The timestamp from the database (1900-01-01T00:00:00Z)
        QDateTime invalidDate(QDate(1900, 1, 1), QTime(0, 0, 0));   // Reference date which marks an invalid timestamp
        
        if (evId > invalidDate)    // TRUE! Why??
            event.insert("ev_id", evId.toString(Qt::ISODate));
        

        Why is the expression of the if in the above code true?

        A.T.

        J Offline
        J Offline
        JonB
        wrote on 4 Nov 2024, 10:11 last edited by
        #3

        @TheoSys
        Just a few weeks ago someone reported a problem parsing datetimes only for PostgreSQL. Might this be related? Try searching the forum, or I will post if I can locate it.

        Otherwise check exactly what you are getting back in evId from the parse. QDateTime has toSecsSinceEpoch() or toStdSysMilliseconds(), call that on both your values to see if/how they differ.

        1 Reply Last reply
        0
        • T Offline
          T Offline
          TheoSys
          wrote on 4 Nov 2024, 10:13 last edited by TheoSys 11 Apr 2024, 10:15
          #4

          @jsulm: I added already a debugging output:
          The code:

          MSG_DEBUG("ev_id: " << evId.toString(Qt::ISODate).toStdString() << " (" << invalidDate.toString(Qt::ISODate).toStdString() << ")");
          

          printed out:

          2024-11-04 10:48:10: DEBUG:   ev_id: 1900-01-01T00:00:00Z (1900-01-01T00:00:00)
          

          The only difference is the missing Z in the QDateTime set in the code. The timezone was never set and the database field is defined as:

          ev_id timestamp without time zone NOT NULL DEFAULT '1900-01-01 00:00:00'::timestamp without time zone
          

          So the timezone should be UTC in any case.

          J 1 Reply Last reply 4 Nov 2024, 10:18
          0
          • T TheoSys
            4 Nov 2024, 10:13

            @jsulm: I added already a debugging output:
            The code:

            MSG_DEBUG("ev_id: " << evId.toString(Qt::ISODate).toStdString() << " (" << invalidDate.toString(Qt::ISODate).toStdString() << ")");
            

            printed out:

            2024-11-04 10:48:10: DEBUG:   ev_id: 1900-01-01T00:00:00Z (1900-01-01T00:00:00)
            

            The only difference is the missing Z in the QDateTime set in the code. The timezone was never set and the database field is defined as:

            ev_id timestamp without time zone NOT NULL DEFAULT '1900-01-01 00:00:00'::timestamp without time zone
            

            So the timezone should be UTC in any case.

            J Offline
            J Offline
            JonB
            wrote on 4 Nov 2024, 10:18 last edited by
            #5

            @TheoSys
            Although it's not the post I recall and tried to search for, have you seen e.g. https://bugreports.qt.io/browse/QTBUG-86450

            Not possible as per e.g. https://www.postgresqltutorial.com/postgresql-timestamp/

            "When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server, the user, or the current database connection."

            Is this relevant? I still suggest you do the comparisons I mentioned so you can (hopefully) diagnose why the test passes and what is going on.

            1 Reply Last reply
            0
            • C Online
              C Online
              Christian Ehrlicher
              Lifetime Qt Champion
              wrote on 4 Nov 2024, 10:22 last edited by
              #6

              The psql driver now stores and retrieves all timestamps as utc - otherwise it is not possible to retrieve a useful value when the server and client has different time zones.

              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 4 Nov 2024, 10:24
              0
              • T Offline
                T Offline
                TheoSys
                wrote on 4 Nov 2024, 10:23 last edited by
                #7

                I tried now to print out the seconds since epoch and this is indeed different. The code

                MSG_DEBUG("Epoch seconds: " << evId.toSecsSinceEpoch() << " (" << invalidDate.toSecsSinceEpoch() << ")");
                

                printed:

                2024-11-04 11:18:02: DEBUG:   Epoch seconds: -2208988800 (-2208992400)
                

                Because the numbers are negative, evId is really greater then invalidDate. This explains the behavior of the if. Looks like this is a problem with PostgreSQL.

                1 Reply Last reply
                0
                • C Christian Ehrlicher
                  4 Nov 2024, 10:22

                  The psql driver now stores and retrieves all timestamps as utc - otherwise it is not possible to retrieve a useful value when the server and client has different time zones.

                  J Offline
                  J Offline
                  JonB
                  wrote on 4 Nov 2024, 10:24 last edited by
                  #8

                  @Christian-Ehrlicher
                  So the bug I just linked is no longer relevant?
                  Christian, I can't find it, but just a few weeks ago I believe some asked here about a problem parsing/converting timestamps of datetime strings from PSQL and I think you answered. Do you recall it?

                  T 1 Reply Last reply 4 Nov 2024, 10:49
                  0
                  • C Online
                    C Online
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on 4 Nov 2024, 10:32 last edited by
                    #9

                    You are right, the parsing failed for 6.8.0 but here it looks like a valid QDateTime is available so I would guess it's the utc thing. This was also mentioned in the release notes as important behavior change. I don't see another way to get a useful datetime from the database otherwise.

                    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 4 Nov 2024, 10:38
                    0
                    • C Christian Ehrlicher
                      4 Nov 2024, 10:32

                      You are right, the parsing failed for 6.8.0 but here it looks like a valid QDateTime is available so I would guess it's the utc thing. This was also mentioned in the release notes as important behavior change. I don't see another way to get a useful datetime from the database otherwise.

                      J Offline
                      J Offline
                      JonB
                      wrote on 4 Nov 2024, 10:38 last edited by JonB 11 Apr 2024, 10:42
                      #10

                      @Christian-Ehrlicher
                      Agreed. But purely FMI, it's annoying me that I cannot locate it, do you recall the link to the post here you made or a Qt bug number? I'd just like to find it :)

                      EDIT Ah, it was https://forum.qt.io/topic/159172/qsqlquery-postgres-and-timestamp-qdatetime-invalid. (Cannot find this from Google, even with title, irritating/surprising.) OP said they had filed a bug report but did not give link.

                      1 Reply Last reply
                      0
                      • J JonB
                        4 Nov 2024, 10:24

                        @Christian-Ehrlicher
                        So the bug I just linked is no longer relevant?
                        Christian, I can't find it, but just a few weeks ago I believe some asked here about a problem parsing/converting timestamps of datetime strings from PSQL and I think you answered. Do you recall it?

                        T Offline
                        T Offline
                        TheoSys
                        wrote on 4 Nov 2024, 10:49 last edited by
                        #11

                        @JonB The difference is precisely 3600 seconds (1 hour). Although my invalidDate should be in UTC time, as well as the one from the database (evId), the timestamp from the database is the local time, which is MET (UTC+1). If my guess is right I have a problem when the program is running in a different time zone. In this special case I can add 24 hours and compare then. But because the timestamps converted to a string are equal something is strange.

                        # date -u -d @-2208988800
                        Mo 01 Jan 1900 00:00:00 UTC
                        # date -u -d @-2208992400
                        So 31 Dec 1899 23:00:00 UTC
                        
                        J 1 Reply Last reply 4 Nov 2024, 10:58
                        0
                        • T TheoSys
                          4 Nov 2024, 10:49

                          @JonB The difference is precisely 3600 seconds (1 hour). Although my invalidDate should be in UTC time, as well as the one from the database (evId), the timestamp from the database is the local time, which is MET (UTC+1). If my guess is right I have a problem when the program is running in a different time zone. In this special case I can add 24 hours and compare then. But because the timestamps converted to a string are equal something is strange.

                          # date -u -d @-2208988800
                          Mo 01 Jan 1900 00:00:00 UTC
                          # date -u -d @-2208992400
                          So 31 Dec 1899 23:00:00 UTC
                          
                          J Offline
                          J Offline
                          JonB
                          wrote on 4 Nov 2024, 10:58 last edited by JonB 11 Apr 2024, 11:02
                          #12

                          @TheoSys
                          Just what was expected --- a UTC/local tz difference! Which is why @Christian-Ehrlicher suggests storing everything and comparing everything in UTC. (An issue I faced years ago, MS SQL Server and not Qt.) Or I believe I see Postgres has AT TIME ZONE, I don't know if you can make use of that when reading from PSQL (perhaps not as Qt/PSQL is generating the SQL to read the value from a datatime field)? Or, although I know nothing about Postgres, I may have come across a (SO?) post for Postgres saying you can do something like "set the timezone for a session" (to UTC) to control tz conversion?

                          C 1 Reply Last reply 4 Nov 2024, 11:04
                          0
                          • J JonB
                            4 Nov 2024, 10:58

                            @TheoSys
                            Just what was expected --- a UTC/local tz difference! Which is why @Christian-Ehrlicher suggests storing everything and comparing everything in UTC. (An issue I faced years ago, MS SQL Server and not Qt.) Or I believe I see Postgres has AT TIME ZONE, I don't know if you can make use of that when reading from PSQL (perhaps not as Qt/PSQL is generating the SQL to read the value from a datatime field)? Or, although I know nothing about Postgres, I may have come across a (SO?) post for Postgres saying you can do something like "set the timezone for a session" (to UTC) to control tz conversion?

                            C Online
                            C Online
                            Christian Ehrlicher
                            Lifetime Qt Champion
                            wrote on 4 Nov 2024, 11:04 last edited by
                            #13

                            @JonB said in QT 6.8.0: Comparison of QDateTime is wrong:

                            something like "set the timezone for a session" (to UTC) to control tz conversion?

                            That's what the psql driver does now - it sets the timezone to utc.

                            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 4 Nov 2024, 11:13
                            0
                            • C Christian Ehrlicher
                              4 Nov 2024, 11:04

                              @JonB said in QT 6.8.0: Comparison of QDateTime is wrong:

                              something like "set the timezone for a session" (to UTC) to control tz conversion?

                              That's what the psql driver does now - it sets the timezone to utc.

                              J Offline
                              J Offline
                              JonB
                              wrote on 4 Nov 2024, 11:13 last edited by JonB 11 Apr 2024, 11:13
                              #14

                              @Christian-Ehrlicher
                              Then, if I understand right, that does not chime with

                              @TheoSys said in QT 6.8.0: Comparison of QDateTime is wrong:

                              the timestamp from the database is the local time, which is MET (UTC+1). If my guess is right I have a problem when the program is running in a different time zone.

                              ? I am lost as to what tzs each of the OP's two QDateTimes are in (he should look at them). I think I should leave it then between you two PSQL users :)

                              T 1 Reply Last reply 4 Nov 2024, 11:33
                              0
                              • J JonB
                                4 Nov 2024, 11:13

                                @Christian-Ehrlicher
                                Then, if I understand right, that does not chime with

                                @TheoSys said in QT 6.8.0: Comparison of QDateTime is wrong:

                                the timestamp from the database is the local time, which is MET (UTC+1). If my guess is right I have a problem when the program is running in a different time zone.

                                ? I am lost as to what tzs each of the OP's two QDateTimes are in (he should look at them). I think I should leave it then between you two PSQL users :)

                                T Offline
                                T Offline
                                TheoSys
                                wrote on 4 Nov 2024, 11:33 last edited by
                                #15

                                @JonB said in QT 6.8.0: Comparison of QDateTime is wrong:

                                ? I am lost as to what tzs each of the OP's two QDateTimes are in (he should look at them). I think I should leave it then between you two PSQL users :)

                                As I wrote above, the database field is defined without a timezone. According to PostgreSQL documentation this means it is stored unchanged and will not change, regardless in what timezone the database server is running.
                                The QDateTime is, according to the documentation, defined as UTC unless a time zone is set. Because of this I assumed that both timestamps are equal, which is not true. However if I do a select (with pgadmin4) like

                                SELECT date_part('epoch', ev_id) FROM event WHERE ev_num = 2109;
                                

                                I get

                                -2208988800
                                

                                Which is the same value as I get from

                                evId.toSecsSinceEpoch()
                                

                                So far so well. This is what I expect. And this should be the correct epoch with UTC time.
                                When I set QDateTime(QDate(1900, 1, 1), QTime(0, 0, 0)) then it seems that Qt is taking this as a local time and subtract 1 hour (in my case). This would explain the difference. To me this looks like an error in Qt. Unless an explicit time zone was specified, the time should be taken as UTC.

                                J 1 Reply Last reply 4 Nov 2024, 12:04
                                0
                                • T TheoSys
                                  4 Nov 2024, 11:33

                                  @JonB said in QT 6.8.0: Comparison of QDateTime is wrong:

                                  ? I am lost as to what tzs each of the OP's two QDateTimes are in (he should look at them). I think I should leave it then between you two PSQL users :)

                                  As I wrote above, the database field is defined without a timezone. According to PostgreSQL documentation this means it is stored unchanged and will not change, regardless in what timezone the database server is running.
                                  The QDateTime is, according to the documentation, defined as UTC unless a time zone is set. Because of this I assumed that both timestamps are equal, which is not true. However if I do a select (with pgadmin4) like

                                  SELECT date_part('epoch', ev_id) FROM event WHERE ev_num = 2109;
                                  

                                  I get

                                  -2208988800
                                  

                                  Which is the same value as I get from

                                  evId.toSecsSinceEpoch()
                                  

                                  So far so well. This is what I expect. And this should be the correct epoch with UTC time.
                                  When I set QDateTime(QDate(1900, 1, 1), QTime(0, 0, 0)) then it seems that Qt is taking this as a local time and subtract 1 hour (in my case). This would explain the difference. To me this looks like an error in Qt. Unless an explicit time zone was specified, the time should be taken as UTC.

                                  J Offline
                                  J Offline
                                  JonB
                                  wrote on 4 Nov 2024, 12:04 last edited by JonB 11 Apr 2024, 12:13
                                  #16

                                  @TheoSys
                                  You were supposed to call QDateTime::timeZone() and QDateTime::timeSpec() on each of the two instances. (I have a feeling timespec() might be different?)
                                  Assuming they are different, then figure back from there why each one is each.

                                  Qt is taking this as a local time and subtract 1 hour (in my case). This would explain the difference. To me this looks like an error in Qt. Unless an explicit time zone was specified, the time should be taken as UTC.

                                  What makes you assert (the second part of) this? Why "error", why do you think it should be taken as UTC? I suspect it's either "local time" or "unspecified". A lot of "default" time handling is taken as local time rather than UTC. If you want your constructed datetime to be taken as UTC I think you need to set that, e.g. by picking the constructor which takes a QTimeZone?

                                  1 Reply Last reply
                                  0
                                  • C Online
                                    C Online
                                    Christian Ehrlicher
                                    Lifetime Qt Champion
                                    wrote on 4 Nov 2024, 12:15 last edited by Christian Ehrlicher 11 Apr 2024, 12:15
                                    #17

                                    https://doc.qt.io/qt-6/qdatetime.html#QDateTime-5

                                    Constructs a datetime with the given date and time, using local time.

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

                                    T 1 Reply Last reply 4 Nov 2024, 12:42
                                    3
                                    • C Christian Ehrlicher
                                      4 Nov 2024, 12:15

                                      https://doc.qt.io/qt-6/qdatetime.html#QDateTime-5

                                      Constructs a datetime with the given date and time, using local time.

                                      T Offline
                                      T Offline
                                      TheoSys
                                      wrote on 4 Nov 2024, 12:42 last edited by
                                      #18

                                      @Christian-Ehrlicher said in QT 6.8.0: Comparison of QDateTime is wrong:

                                      Constructs a datetime with the given date and time, using local time.

                                      I overlooked this. Then it's my fault.

                                      C 1 Reply Last reply 4 Nov 2024, 12:58
                                      1
                                      • T TheoSys has marked this topic as solved on 4 Nov 2024, 12:48
                                      • T TheoSys
                                        4 Nov 2024, 12:42

                                        @Christian-Ehrlicher said in QT 6.8.0: Comparison of QDateTime is wrong:

                                        Constructs a datetime with the given date and time, using local time.

                                        I overlooked this. Then it's my fault.

                                        C Online
                                        C Online
                                        Christian Ehrlicher
                                        Lifetime Qt Champion
                                        wrote on 4 Nov 2024, 12:58 last edited by
                                        #19

                                        @TheoSys said in QT 6.8.0: Comparison of QDateTime is wrong:

                                        Then it's my fault

                                        No problem. Datetime with databases is not that easy as it seems so there might still be problems in the drivers even though I spent a lot of time with this 🙂

                                        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

                                        5/19

                                        4 Nov 2024, 10:18

                                        14 unread
                                        • Login

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