scarhand scarhand - 10 months ago 47
Android Question

SQLite select all records for today and previous day

I have the following table in my database:

database.execSQL("create table " + TABLE_LOGS + " ("
+ COLUMN_ID + " integer primary key autoincrement,"
+ COLUMN_ID_DAY_EXERCISE + " integer not null,"
+ COLUMN_REPS + " integer not null,"
+ COLUMN_WEIGHT + " real not null,"
+ COLUMN_1RM + " real not null,"
+ COLUMN_DATE + " integer not null"
+ ")");


I store a unix timestamp in the COLUMN_DATE field (integer).

Now I have the following function which grabs all of the records:

public Cursor fetchCurrentLogs(long dayExerciseDataID) {
// where day = today
Cursor cursor = database.rawQuery("select " + MySQLiteHelper.COLUMN_ID + "," + MySQLiteHelper.COLUMN_REPS + ", " + MySQLiteHelper.COLUMN_WEIGHT + " " +
"from " + MySQLiteHelper.TABLE_LOGS + " " +
"where " + MySQLiteHelper.COLUMN_ID_DAY_EXERCISE + " = '" + dayExerciseDataID + "'", null);
if (cursor != null) { cursor.moveToFirst(); }
return cursor;
}


Now what I want to do, is I want to make this function only grab the records for today.

Then I want to make another function exactly the same, however instead of getting the records for today, I want it to get the records for the previous day. By previous, I mean the most recent day that has records that is not today. So it could be yesterday, or 3 days ago, or a month ago.

I know in MySQL you can do something like this for the current day:

where date_format(from_unixtime(COLUMN_DATE), '%Y-%m-%d')= date_format(now(), '%Y-%m-%d')


What is the equivalent to this for SQLite?

Also, can anyone help me with the where clause for the previous day as mentioned above?

Thanks so much.

Answer Source
String sql = "SELECT * FROM myTable WHERE myDate >= date('now','-1 day')"; 
Cursor mycursor = db.rawQuery(sql);

EDIT:

SELECT * from Table1 where myDate = (select max(myDate) from Table1 WHERE myDate < DATE('now') )