Filter by Date on a SQLITE

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

  • Lifetime Qt Champion


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

    Hope it helps

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


    "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;
    sqlite> select * from test order by date2 asc;

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