CODEWITHSUNDEEP
androidsqliteandroid-cursor
PrincessLeiha
PrincessLeiha

Reputation: 3174

Sort DB data and display in list view

I get data from my DB using 

cursor = db.query("WebLeadMaster", select, "condition1="+conditionVal1+
" and condition2="+ConditionVal2,null, null, null, "RegistrationTime DESC");

I am getting the data in my cursor alright. To display the data, i use the following code:

    SimpleCursorAdapter mAdapter = new SimpleCursorAdapter(this, 
            R.layout.resultleadlookup, cursor, columns, to); 
    mAdapter.notifyDataSetChanged();
    lstResult.setAdapter(mAdapter);

So, I cannot modify the contents of the cursor. The sort query has condition "RegistrationTime" which is a String data type. As you can see in the image below, it is not in a proper sorted format. (not according to date-time).

What changes should i make in my code so that it would sort properly according to date-time?

expected output and actual output

If i alter my DB query, to look like 

cursor = db.query("WebLeadMaster", select, "condition1="+conditionVal1+
" and condition2="+ConditionVal2,null, null, null, null);

it gives an ascending order. All i want it the descending order.

Upvotes: 4

Views: 3831

Answers (4)

Mayur More
Mayur More

Reputation: 981

If you r using ORM you can sort the data by timestamp. ORM makes data insertion and data retrieval easier from database. You have to include jar files to your project to use ORM...

Upvotes: 1

PrincessLeiha
PrincessLeiha

Reputation: 3174

Well, i changed my table structure. I added another field "_id" to it. Set the property as AUTO INCREMENT to it, and sorted the list with respect to _id field.

Upvotes: 2

Selvin
Selvin

Reputation: 6797

since SQLite has no datetime type, store date type as LONG/INT/NUMERIC in SQLite (EPOCH time) it will be easier to sort

then add ViewBinder to Adapter

        /*field in activity/fragment*/
        final static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //or other format that you wana to show in ListView
        ...
        mAdapter.setViewBinder(new SimpleCursorAdapter.ViewBinder() {

            @Override
            public boolean setViewValue(View view, Cursor cursor,
                    int columnIndex) {
                final int id = view.getId();
                switch (id) {
                    case R.id.id_of_textview_with_date_data:
                        final Calendar cal = Calendar.getInstance();
                        cal.setTimeInMillis(cursor.getLong(columnIndex));
                        ((TextView) view).setText(sdf.format(cal.getTime()));
                        return true;
                }
                return false;
            }
        });

... or as dennisg pointed store it as STRING/VARCHAR edit: in "yyyy-MM-dd HH:mm:ss" format

Upvotes: 0

dennisg
dennisg

Reputation: 4368

The easiest suggestion would be to save the date in a different format (but still saved as string) into the database. If you would save the data into SQLite’s default date format (YYYY-MM-DD HH:NN:SS), you can easily sort the dates.

To display the date in your format, you would only just need to reformat the date into the correct format.

Upvotes: 2

Related Questions