SQLite select all records for today and previous day
Solution 1
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') )
Solution 2
See the date/time functions documentation:
SELECT *
FROM MyTable
WHERE myDate >= date('now', '-1 days')
AND myDate < date('now')
scarhand
Updated on July 27, 2022Comments
-
scarhand almost 2 years
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.
-
scarhand about 11 yearsI need to only select the records for the previous day, not all previous days. Know what I mean? So lets say I have entries in the database for today, and 3 days ago, and 6 days ago, I want one function to get the records for today, and the other function to get the records for 3 days ago.
-
Riddhi Shah about 7 yearsHi I need to fetch records of last 5 minutes of current date then how can I achieve this? @Yaqub