CODEWITHSUNDEEP
androidsqlitedateandroid-sqlite
odgatelmand
odgatelmand

Reputation: 413

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.

Upvotes: 2

Views: 2862

Answers (3)

GVillani82
GVillani82

Reputation: 17429

First of all, storing date in milliseconds is not a good idea. In according to the Sqlite documentation (section 1.2), you should store your date in one of the following ways:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

After that, it doesn't metter how you actually store your dates, you can always manage it using strftime.

In your case you can use the following code:

SELECT SUM(amount) FROM transactions 
WHERE strftime('%m', transactionDate) = '05' // replace '05' with your month

Look at this fiddle, I have just created. As you can see there, the datetime can be stored in different ways, but the strftime can act over all datetime in the same way.

Upvotes: 4

Alejandro Cumpa
Alejandro Cumpa

Reputation: 2353

I suggest this query:

"SELECT sum(amount) FROM" + MyDbTable +
"WHERE " + COLUMN_DATE + " between date('"+year +"-"monthNumber+"-01') and 
date('"+year +"-"monthNumber+"-01",'start of month','+1 month','-1 day')"

or something like that, try to get this: 

SELECT sum(amount) FROM test WHERE date(date) 
BETWEEN date('2015-01-01') AND date('2015-02-01')

I can't test the code right now, sorry.

Upvotes: 1

Michael Pardo
Michael Pardo

Reputation: 2600

It's probably better to calculate your range first and use BETWEEN, like this:

public int getIncomeOfMonth(int monthNumber) {
    final Calendar calendar = Calendar.getInstance();

    calendar.add(Calednar.MONTH, monthNumber)
    long start = calendar.getTimeInMillis();

    calendar.add(Calendar.MONTH, 1);
    long end = calendar.getTimeInMillis()

    Cursor cursorIncome = db.rawQuery(
            "SELECT sum(amount) FROM " + MyDbTable +
            " WHERE " COLUMN_DATE + " BETWEEN " + start + " AND " + end, null);


    int positiveTransacSelectedMonth = 0;

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

    return positiveTransacSelectedMonth;
}

Upvotes: 1

Related Questions