I have a room database with a table "Entries" where I have the columns value(int), cat_id(int) and date(Date).
I want to get the sum of all entries of the current month and all entries of the current week. But the selections:
@Query("SELECT sum(value) FROM Entry WHERE cat_id = :cat_id AND WEEK(date, 5) = WEEK(:date, 5) AND YEAR(date) == YEAR(:date)")
float getValueThisWeekByCatId(int cat_id, Date date);
@Query("SELECT sum(value) FROM Entry WHERE cat_id = :cat_id AND MONTH(date) = MONTH(:date) AND YEAR(date) == YEAR(:date)")
float getValueThisMonthByCatId(int cat_id, Date date);
don't work.
This is the error:
There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such function: WEEK) float getValueThisWeekByCatId(int cat_id, Date date); Same with function MONTH
Is there another way to that selection?
A little late but I stumbled upon this question because of a similar problem, and your solution with strftime in the answer is correct.
Tried with your table and query and it worked for me:
CREATE TABLE Entry(
id int primary key,
cat_id int,
value int,
date date
);
insert into entry (id, cat_id, value, date) values (1, 1, 50, '2019-03-19');
insert into entry (id, cat_id, value, date) values (2, 1, 30, '2019-02-20');
SELECT sum(value)
FROM Entry
WHERE cat_id = 1
AND strftime('%m', date) = strftime('%m', '2019-03-01')
AND strftime('%Y', date) = strftime('%Y', '2019-03-01')
Also works with CURRENTDATE but obviously have to use current date entries.
