CODEWITHSUNDEEP
androidsqlitedate
Leroy Boerefijn
Leroy Boerefijn

Reputation: 39

Android get items from sqlite db with date of today or previous

I'm struggling with the implementation of dates in SQLite3 (for android). According to the documentation (https://www.sqlite.org/datatype3.html and http://www.sqlite.org/lang_datefunc.html), SQLite doesn't have datatypes for date and time specifically and therefore it can be either stored as a TEXT or as an INTEGER. I've tried both, but they give the same erroneous results. After reading a lot on the internet and trying everything I can think of, I come here as a last resort.

So, now for the problem. The idea is actually very simple. I have a table containing items, with a date column. Now I want to select all items from this database that have a date of today, or before (i.e. today or in the past). In my current implementation I store dates as integers, since most people seem to agree that that is the way to go.

Below is the (simplified) code for inserting an item.

Calendar calendar = GregorianCalendar.getInstance();
calendar.set(Calendar.HOUR_OF_DAY, 0); // Might not be necessary
calendar.add(Calendar.DAY_OF_YEAR, 4); // Today plus 4 days

SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_DATE, calendar.getTimeInMillis()); // Today as integer/long
long id = db.insert(TABLE_ITEMS, null, values); // Add to db

And next I want to select the items where the date is equal to or lower than today:

SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
String[] columns = new String[]{COLUMN_ID, COLUMN_TEXT, COLUMN_DATE};
String[] selectArg = new String[]{"date('now')"};
Cursor cItems = db.query(TABLE_ITEMS, columns, COLUMN_DATE + "<= ?", selectArg, null, null, null);
//Cursor cItems = db.query(TABLE_ITEMS, columns, "strftime('%Y-%m-%d'," + COLUMN_DATE + ")" + select, selectArg, null, null, null);
//Cursor cItems = db.query(TABLE_ITEMS, columns, "date(" + COLUMN_DATE + ")" + select, selectArg, null, null, null);

Now all rows are selected, also the rows with a date in the future. Could anyone tell me what I need to do differently?

Thanks in advance!

Edit

I found out that sqlite stores date integers in seconds (is that correct?). So that would mean that value I put in the database should be Math.round(calendar.getTimeInMillis()/1000, to get it in seconds right? But then it makes even less sence, since a date in milliseconds should always be larger than a date in seconds. Anyhow, I tried that, but it doesn't work either.

Upvotes: 2

Views: 3712

Answers (2)

Leroy Boerefijn
Leroy Boerefijn

Reputation: 39

Thanks to the insights CL. gave me, I fixed some trivial errors I made.

Looking back at the SQLite documentation, I figured that there are two ways of storing a date without a time. A Julian day number or a date string YYYY-MM-DD. There is no readily available function to get a Julian day number in java/android, so therefore I chose to go back to the string format again.

A second, mistake I figured out just now is that the selectArgs are considered text and therefore, putting the date('now', 'localtime') in there won't work.

Below are the final scripts that do work.

Calendar calendar = GregorianCalendar.getInstance();
calendar.set(Calendar.HOUR_OF_DAY, 0); // Might not be necessary
calendar.add(Calendar.DAY_OF_YEAR, 4); // Today plus 4 days

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
ContentValues values = new ContentValues();
values.put(COLUMN_DATE, dateFormat.format(calendar.getTime())); // Date as string


SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
long id = db.insert(TABLE_ITEMS, null, values); // Add to db

And to get the data back:

SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
String[] columns = new String[]{COLUMN_ID, COLUMN_TEXT, COLUMN_DATE};
String[] selectArg = new String[]{};
Cursor cItems = db.query(TABLE_ITEMS, columns, COLUMN_DATE + "<= date('now', 'localtime')", selectArg, null, null, null);

CL., many thanks for your help! In the end it was so simple...

Upvotes: 2

CL.
CL.

Reputation: 180030

Neither date() nor strftime('%Y-%m-%d') returns a date in your format.

To convert a date into the seconds format, you would have to use strftime('%s', ...), and convert the resulting string into a number.

Upvotes: 1

Related Questions