lundi 4 mai 2015

Select a group of values from a specific month using Sqlite Query

I have a database with different transactions order by date :

column 1 : transactions amounts

column 2 : dates (in milliseconds)

I want to Sum all the transactions of a specific month. For example : January 2015. My problem is the same as this post : Sum over values by month in milliseconds but i don't arrive to adapt it to my problem and my reputation doesn't allow me to interact with the post.

Here is my code :

public int getIncomeOfMonth(int monthnumber) {

    int year = Calendar.getInstance().get(Calendar.YEAR);

    Cursor cursorIncome = db.rawQuery(
            "SELECT sum(amount) FROM " + MyDbTable +
            " WHERE " + " strftime('%" + year + "-%" + monthnumber + ", " + COLUMN_DATE + "/ 1000, 'unixepoch') ", null);


    int positiveTransacSelectedMonth = 0;

    if(cursorPositiveTransac.moveToFirst()) {
        positiveTransacSelectedMonth = (cursorPositiveTransac.getDouble(0));
    }

    return positiveTransacSelectedMonth;

}

I've read the documentation on the Sqlite website, but can't find a way to understand. Can you explain me how to use strftime in my case or correct my code ? Thanks for your help.

Aucun commentaire:

Enregistrer un commentaire