SQLite的选择今天和previous一天的所有记录 [英] SQLite select all records for today and previous day

问题描述

我下表在我的数据库：

  database.execSQL（CREATE TABLE+ TABLE_LOGS +（
            + COLUMN_ID +整数主键自动增量
            + COLUMN_ID_DAY_EXERCISE +整数NOT NULL，
            + COLUMN_REPS +整数NOT NULL，
            + COLUMN_WEIGHT +实不为空，
            + COLUMN_1RM +实不为空，
            + COLUMN_DATE +整非空
            +））;
 

我存储在COLUMN_DATE场（整数）Unix时间戳。

现在我有以下几点功能，抓住所有的记录：

 公共光标fetchCurrentLogs（长dayExerciseDataID）{
    //其中天=今天
    光标光标= database.rawQuery（选择+ MySQLiteHelper.COLUMN_ID +，+ MySQLiteHelper.COLUMN_REPS +，+ MySQLiteHelper.COLUMN_WEIGHT ++
            从+ MySQLiteHelper.TABLE_LOGS ++
            其中+ MySQLiteHelper.COLUMN_ID_DAY_EXERCISE +='+ dayExerciseDataID +'，NULL）;
    如果（！指针= NULL）{cursor.moveToFirst（）; }
    返回游标;
}
 

现在我想做的事，就是我想使这个功能只抢了记录今天。

然后我想再拍功能完全一样，但不是获取记录今天，我希望它获得了$ P $记录pvious一天。由previous，我指的是最近的一天，有记录的今天，是不是。所以它可能是昨天，还是3天前，或在一个月前。

我知道在MySQL中，你可以做这样的事情当天的：

其中DATE_FORMAT（FROM_UNIXTIME（COLUMN_DATE），'％Y-％M-％D'）= DATE_FORMAT（NOW（），'％​​Y-％M-％D'）

什么是相当于这个SQLite的？

此外，任何人都可以帮我为previous日在where子句如上所述？

太感谢了。

解决方案

 字符串的sql =SELECT * FROM myTable的WHERE数值指明MyDate＆GT; =日期（'现在'，' -  1一天）;
光标mycursor = db.rawQuery（SQL）;
 

编辑：

  SELECT * FROM表1，其中数值指明MyDate =（从表1 MAX（数值指明MyDate），其中数值指明MyDate＆LT; D​​ATE（'现在'））
 

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.

解决方案

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') )

这篇关于SQLite的选择今天和previous一天的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！