java - 在 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());
    nextMonthCal.add(Calendar.MONTH,1);
    Date nextMonth = nextMonthCal.getTime();
    this.db = getReadableDatabase();
    Cursor transactionRecordMonthly = this.db.rawQuery("SELECT * FROM `Transaction` WHERE transactionDate BETWEEN '"+startMonth+"' AND '"+nextMonth+"'",null);
    transactionRecordMonthly.moveToFirst();
    return transactionRecordMonthly;
}

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

Answer 1

我认为 Calendar.WEEK_OF_MONTH 没有达到您的预期。请尝试添加 7 天。