Android: Save Date Normally To Sqlite But Sort By Date Considering Month And Day

I have seen number of post about storing date. I am still not getting the fine and exact approach about saving it to a sqlite database. I am able to store it, but during sorting

Solution 1:

You should start by checking out the SQLite documentation of date & time functions.

For instance, to solve your problem "And what will be the query if i want to get the row whose date is 2 or 3 days in advance" you'd use julian day calculations, such as this example that you can execute directly in the sqlite3 shell:

createTABLE example (_id INTEGERPRIMARY KEY NOTNULL, date TEXT NOTNULL);
insertinto example (date) values ('2011-01-02');
insertinto example (date) values ('2011-04-02');
insertinto example (date) values ('2012-02-26');
insertinto example (date) values ('2012-02-27');
insertinto example (date) values ('2012-02-28');
insertinto example (date) values ('2012-02-29');
insertinto example (date) values ('2012-03-01');
insertinto example (date) values ('2012-03-02');
insertinto example (date) values ('2012-03-03');
selectdatefrom example where julianday(date) - julianday('now') <3AND julianday(date) - julianday('now') >0;

This would return (given that "today" is feb 28th) all the days that are one, two or three days in the future:

2012-02-292012-03-012012-03-02

Edit: To only return rows, regardless of year, you could do something like this - using a VIEW (again, exampl is directly in SQLite):

createview v_example asselect _id, date,
    strftime("%Y", 'now')||'-'||strftime("%m-%d", date) as v_date from example;

This VIEW would return the date & times in your database "rebased" on the current year - which, of course could introduce all manner of wonky behavior with leap years.

You can select all the dates like this in that case:

select date from v_example where 
    julianday(v_date) - julianday('now') < 3 AND
    julianday(v_date) - julianday('now') > 0ORDER BY v_date;

Which would return:

2012-02-292012-03-012001-03-012012-03-022010-03-02

Solution 2:

If you want to sort by day and month consider storing the date as string in the format ddMMyyyy (you need two digits for day and month, otherwise the sorting will be flawed). Sorting by increasing values will give you dates sorted by day and month (and then year).

You can even do range query with string but you have to compute the query string.

Alternatively you may store the date as milliseconds in an additional column (this is the usual format for dates in the database) and do the range queries more easily with integer arithmetic.

Solution 3:

One option is to use strftime() in SQLite to strip of the year and then do a comparison.

select * from sometable wherestrftime('%m-%d', somecolumn) = '02-28'

This will do a query of all rows for February 28th. But performance might be hurt if you have a large table and need to do a string conversion of every row for comparison. Maybe store the day and month in two additional columns if this query is performed often?