在SQLite中使用Between不返回每周数据

Question

 @Override

public void onCreate(SQLiteDatabase db) {

    this.db = db;

    db.execSQL(

            "CREATE TABLE `Transaction` (" +

            "_id integer primary key autoincrement," +

            "`categoryID` INT NOT NULL , " +

            "`incomeAmount` INT NOT NULL, " +

            "`expenseAmount` INT NOT NULL, " +

            "`note` VARCHAR(50) NOT NULL , " +

            "`transactionDate` INT NOT NULL, " +

            "`repeatType` INT NOT NULL  , " +

            "`transactionType` BOOLEAN NOT NULL )"

    );

}

public Cursor readDatabaseTransactionWeekly(Date date){

    Calendar cal = Calendar.getInstance();

    cal.setTime(date);

    cal.set(Calendar.HOUR_OF_DAY, 0);

    cal.clear(Calendar.MINUTE);

    cal.clear(Calendar.SECOND);

    cal.clear(Calendar.MILLISECOND);

    cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek());

    Date startWeek = cal.getTime();

    Calendar nextWeelCal = Calendar.getInstance();

    nextWeelCal.setTime(cal.getTime());

    nextWeelCal.add(Calendar.WEEK_OF_MONTH,1);

    Date nextWeek = nextWeelCal.getTime();

    Log.d(TAG, "readDatabaseTransactionWeekly: "+startWeek+" to "+nextWeek+"");

    this.db = getReadableDatabase();

    Cursor transactionRecordWeekly = this.db.rawQuery("SELECT * FROM `Transaction` WHERE transactionDate BETWEEN '"+startWeek+"' AND '"+nextWeek+"'",null);

    transactionRecordWeekly.moveToFirst();

    return transactionRecordWeekly;

}

public Cursor readDatabaseTransactionMonthly(Date date){

    Calendar cal = Calendar.getInstance();

    cal.setTime(date);

    cal.set(Calendar.HOUR_OF_DAY, 0);

    cal.clear(Calendar.MINUTE);

    cal.clear(Calendar.SECOND);

    cal.clear(Calendar.MILLISECOND);

    cal.set(Calendar.DAY_OF_MONTH, 1);

    Date startMonth = cal.getTime();

    Calendar nextMonthCal = Calendar.getInstance();

    nextMonthCal.setTime(cal.getTime());

}

我试图按周和按月从Andoroid中的SQLite读取数据，并且正在使用SQLite BETWEEN语句。但是它没有按预期工作。每周部分根本不返回任何数据，而按月工作正常则不显示当前周的数据。我调查了另外4个stackoverflow问题，但无法解决。

Answer 1

我认为Calendar.WEEK_OF_MONTH不能达到您的期望。尝试增加7天。