Filter by Date on a SQLITE
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 :)
One starting point is the Date and Time Functions documentation page of SQLite.
Hope it helps
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.
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>
Thanks for your help.