Filter by Date on a SQLITE

[cxam]

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

[SGaist]

Hi,

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

Hope it helps

[the_]

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

[cxam]

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