Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Update: Forum Guidelines & Code of Conduct


    Qt World Summit: Early-Bird Tickets

    Solved Filter by Date on a SQLITE

    General and Desktop
    sqlite date qdate qstring query
    3
    4
    6004
    Loading More Posts
    • 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.
    • cxam
      cxam last edited by

      Hi!
      I have the necessity to filter by date some information stored on a database, the data on the database is stored like text with the following format "dd/MM/yyyy" so the user is able to select all the information between two dates and then the information is displayed on a qtablewidget. I hope you can help me getting the correct SQL query.

      (The user introduces the date by a Graphic QDateEdit)

      Thanks in advance :)

      Stay Hungry, Stay Foolish

      the_ 1 Reply Last reply Reply Quote 0
      • SGaist
        SGaist Lifetime Qt Champion last edited by

        Hi,

        One starting point is the Date and Time Functions documentation page of SQLite.

        Hope it helps

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        1 Reply Last reply Reply Quote 0
        • the_
          the_ @cxam last edited by

          @cxam

          You could add another column to your database where you store the date as unix timestamp.
          Your query could be like this then:

          QSqlQuery q;
          q.prepare("SELECT * from table where timestamp between :time1 AND :time2");
          //time1 and time2 would be int values
          

          btw:

          "dd/MM/yyyy" as date format is not really a good choice. If you try to sort your tables on date, it gives a wrong order with your format. If you youse the defalt SQL date format it gives you the correct order.

          For example

          sqlite> select * from test order by date1 asc;
          01/04/2015|2015-04-01
          02/02/2014|2014-02-32
          05/11/2013|2013-11-05
          31/01/2014|2014-01-31
          sqlite> select * from test order by date2 asc;
          05/11/2013|2013-11-05
          31/01/2014|2014-01-31
          02/02/2014|2014-02-32
          01/04/2015|2015-04-01
          sqlite> 
          

          -- No support in PM --

          cxam 1 Reply Last reply Reply Quote 0
          • cxam
            cxam @the_ last edited by

            @the_ @SGaist Hi, I managed to solve it on my own by encoding the date to a JulianDate and using juliandate on my db so it's much easier to compare and so.

            Thanks for your help.

            Stay Hungry, Stay Foolish

            1 Reply Last reply Reply Quote 0
            • First post
              Last post