Get data from current week and month in the Room Database

Question

I'm trying to get data from current Week and Month in Room. I've created a current Day query and it works, but the others do not work.

Day query (this one works):

 @Query("SELECT * FROM expense_table WHERE day = strftime('%d', 'now')")
LiveData<List<Expense>> getExpensesDay();

Week:

@Query("SELECT * FROM expense_table WHERE week = strftime('%W', 'now')")
LiveData<List<Expense>> getExpensesWeek();

Month:

@Query("SELECT * FROM expense_table WHERE month = strftime('%m', 'now')")
LiveData<List<Expense>> getExpensesMonth();

Entity:

@Entity(tableName = "expense_table")
public class Expense  {

@PrimaryKey(autoGenerate = true)
private int expenseId;
private String note;
private Double value;
private String type;
private Long dateLong = System.currentTimeMillis();
private String date = new SimpleDateFormat("MM/yyyy").format(new Date(dateLong));
private static Calendar cal = Calendar.getInstance();
private int month = cal.get(Calendar.MONTH);
private int week = cal.get(Calendar.WEEK_OF_YEAR);
private int day = cal.get(Calendar.DAY_OF_MONTH);
private int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);
private String weekDay = new DateFormatSymbols().getWeekdays()[dayOfWeek];

Answer 1

According to docs, strftime('%W', ...) substitutes week of year with 0-based, and Calendar.get(Calendar.WEEK_OF_YEAR) returns week of year with 1-based. Similarly, strftime('%m', ...) substitutes week of year with 1-based, and Calendar.get(Calendar.MONTH) returns week of year with 0-based.

So, try to replace calculation of fields in your Entity class:

private int month = cal.get(Calendar.MONTH) + 1;
private int week = cal.get(Calendar.WEEK_OF_YEAR) - 1;

instead of

private int month = cal.get(Calendar.MONTH);
private int week = cal.get(Calendar.WEEK_OF_YEAR);