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

    Hi,

    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
    

    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> 
    


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