filtering result from past 7 days in sqlite database in android

Question

how do I get result of this week filtered from sqlite database in android. I have stored date in form of 2016-12-04. Now when user clicks a button i want to show result(sum) of past seven days in android.

 if(all=="This Week")

    {
        query=("Select SUM("+ DataConstants.amount_name+") FROM " + DataConstants.table_name + "WHERE "+ DataConstants.date_name+ "="+ "(WHAT SHOULD I KEEP HERE?)");  
        cursor= dba.rawQuery(query,null);}

`"="+ "(WHAT SHOULD I KEEP HERE?)"` for sure not "=" ... the date in string format `yyyy-MM-dd` is comparable in the same way as dates ... so `dateColum >= concrateDateInSameFormat` should works ... in othe words **replace `(WHAT SHOULD I KEEP HERE?)` with `?` and add parameter which is date 7 days ago in proper string format** and replace `=` with `>=` — Selvin, Dec 06 '16 at 13:58
*`all=="This Week"`* also you should learn some java's basics — Selvin, Dec 06 '16 at 14:03

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

In SQLite you can get the current date and substract 7 days from it:

query = String.format(
    "SELECT SUM(%s) FROM %s WHERE %s >= DATETIME('now', '-7 day');",
    DataConstants.amount_name,
    DataConstants.table_name,
    DataConstants.date_name);
cursor = dba.rawQuery(query, null);

Also, you should change if (all == "This Week") to if (all.equals("This Week"))

edited May 23 '17 at 12:24

Community

1
1

answered Dec 06 '16 at 14:02

DATETIME('now', '-7 day');", what should i write insteadof this – hamro doko Dec 06 '16 at 15:02
What do you mean? If you want data of a week ago then this is the query you need. – Dec 06 '16 at 15:04
i did not get answer with this. It returned the same value of sum as it was for total. It didnot filter sum for 7 days. – hamro doko Dec 06 '16 at 15:21

filtering result from past 7 days in sqlite database in android

1 Answers1