Geek asks and answers

SQLite selects all records for today and the previous day

My database has the following table:

    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 save the unix timestamp in the COLUMN_DATE field (integer).

Now I have the following function that captures all 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, I want this feature to only capture recordings today.

Then I want to make another function exactly the same, but instead of getting records for today, I want her to get records for the previous day. I used to mean the very last day, which has notes that are not today. So it can be yesterday, or 3 days ago, or a month ago.

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

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

What is equivalent to this for SQLite?

, - where , ?

.

+5
4
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') )
+14

/:

SELECT *
FROM MyTable
WHERE myDate >= date('now', '-1 days')
  AND myDate <  date('now')
+3

, , -. , , , , ...

    String selectQuery = "SELECT * FROM "+Your_table+" 
WHERE ("+Column_1+" = "+Value_For_Column1+" AND date("+Your_Column_With_Time_Values+") == date('now')) AND 

"+Some_Other_Column+" = "+Some_Other_Value+" OR "+Some_Other_Column+" = "+Some_Other_Value+" ORDER BY "+Your_Column+" DESC";

- , - . !

0

SQL. Java-. Java- SQL. , , Java DateTime unix.

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 + 
            " OR " + MySQLiteHelper.COLUMN_ID_DAY_EXERCISE + " = '" + previousDay(dayExerciseDataID) +
"'", null);

previousDay.

-2

More articles:


All Articles