CODEWITHSUNDEEP
androiddatabasesqlite
Marc
Marc

Reputation: 1184

Getting total out of sqlite database

I have a database (sqlite on Android) with several columns: _id, datum, hour, note

The database is created by this:

    private static final String DATABASE_CREATE =
    "create table worked (_id integer primary key autoincrement, "
    + "datum text not null, hour text not null, " 
    + "note text);";

At the end I want the total of hours and minutes out of my database and return it to my mainActivity.

I tried to do this:

public int getTotalHours(){
    Cursor c = db.rawQuery("select hour from " + DATABASE_TABLE, null);
    int total = 0;
    Date hours = new Date();
    SimpleDateFormat hourFormat = new SimpleDateFormat("HH:mm");
    c.moveToFirst();
    for(int i = 0; i<c.getCount(); i++){
        String uur = c.getString(i);
        try {
            hours = hourFormat.parse(uur);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        total += hours.getHours();
    }
    return total;
}

But I'm already getting a error in my query. I also dont know how if the data is being extracted at the right way, I cant test that because of the query error...

The error I'm getting:

Failed to read row 0, column 1 from a cursorWindow which has 5 rows, 1 columns.

I hope I was in the right way, and that somebody can help me. Thanks in advance!

Upvotes: 2

Views: 292

Answers (6)

Diego Torres Milano
Diego Torres Milano

Reputation: 69208

First, it's a table in a database what you are creating so I suggest you use CREATE_TABLE variable name instead. Second, you can do:

Cursor c = db.rawQuery("select sum(hour) from " + DATABASE_TABLE, null);

having defined hour as integer in you table.

If you define the correct columns and use the right operations sqlite can do it:

sqlite> .schema t1
CREATE TABLE t1 (hours time not null);
sqlite> select * from t1;
05:30:00
06:30:00
01:30:00
02:15:00
sqlite> select strftime('%H:%M', 946684800 + sum(strftime('%s', hours) - 946684800), 'unixepoch') from t1;
15:45
sqlite>

Upvotes: 0

Mr.Me
Mr.Me

Reputation: 9276

Please make sure that the hours row is the first one in the db table. if you're not sure this modified code should work

enter code here
public int getTotalHours(){
Cursor c = db.rawQuery("select hour from " + DATABASE_TABLE, null);
int total = 0;
Date hours = new Date();
SimpleDateFormat hourFormat = new SimpleDateFormat("HH:mm");
c.moveToFirst();
int column = c.getColumnIndex("id")
for(int i = 0; i<c.getCount(); i++){
    String uur = c.getString(c);
    try {
        hours = hourFormat.parse(uur);
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    total += hours.getHours();
}
return total;

}

Upvotes: 0

dymmeh
dymmeh

Reputation: 22306

Properly loop through your records, retrieve the proper column index, and close your cursor:

public int getTotalHours(){
    Cursor c = db.rawQuery("select hour from " + DATABASE_TABLE, null);
    if (c == null)
        return 0;

    try{
        int total = 0;
        Date hours = new Date();
        SimpleDateFormat hourFormat = new SimpleDateFormat("HH:mm");
        while(c.moveToNext())
        {
            String uur = c.getString(0);
            try {
                hours = hourFormat.parse(uur);
                total += hours.getHours();
            } catch (ParseException e) {
                e.printStackTrace();
            }           
        }

        return hours;
    }finally{
        c.close();
    }
}

Upvotes: 1

Philip Sheard
Philip Sheard

Reputation: 5825

Calling c.moveToNext() at the end of the loop would help. Also c.getString(i) should be c.getString(0).

Upvotes: 0

Jade Byfield
Jade Byfield

Reputation: 4816

In your for loop, start your int i off at 1 instead of 0

for(int i = 1; i<c.getCount(); i++){
    String uur = c.getString(i);
    try {
        hours = hourFormat.parse(uur);
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

Upvotes: 0

Georgy Gobozov
Georgy Gobozov

Reputation: 13731

use

String uur = c.getString(1);

Upvotes: 0

Related Questions