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.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.