scarhand scarhand - 28 days ago 6
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
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') )