How can i get SQLite to sort by date properly using YYYY-MM-DD format?

Question

I'm making an app that needs to sort a Sqlite table of weeks by date and display just the date and the total hours worked that week in a listview.

My activity extends listview and uses SimpleCursorAdapter to populate the listview. My date column is a TEXT field formatted like this "YYYY-MM-DD"

Here is my database query function, I base my database helper class off of the google notepad example.

public Cursor getAllWeeks()
    {
        Cursor mCursor = mDb.query(true, DATABASE_TABLE, null, null, null, null, null, "weekdate ASC", null);


        if (mCursor != null)
        {
            mCursor.moveToFirst();
        }

        return mCursor;
    }

And I hook it to the ListView like this:

public class WeekList extends ListActivity implements OnClickListener{
    final static int DELETE_ID = Menu.FIRST + 1;

    public static NotesDbAdapter DBadapter;
    public static Cursor ListCursor;

    static final String[] displaycolumns = {"weekdate", "weektotalhours"};
    static final int[] listitemviews = {R.id.TextViewListItem1, R.id.TextViewListItem2};

    @Override
    public void onCreate(Bundle icicle)
    {
        super.onCreate(icicle);
        setContentView(R.layout.weeklist);
        Log.i("bendebug", "logtest");

        DBadapter = new NotesDbAdapter(this);
        DBadapter.open();
        ListCursor = DBadapter.getAllWeeks();


        this.setListAdapter(new SimpleCursorAdapter(this, R.layout.listitemlayouts, ListCursor, 
                displaycolumns, listitemviews));
        findViewById(R.id.ButtonAddWeek).setOnClickListener(this);

        //Activate the context menu
        registerForContextMenu(getListView());

    }

The problem I am having is that my cursor doesn't seem sorted properly, my weeklist looks like this:

2010-7-19
2010-7-23
2010-7-24
2010-7-6

Instead of being properly sorted. I'm probably missing something really obvious, since I'm a total noob trying to teach myself.

Obviously i shortened the code to make it more readable, since the rest of the activity is irrelevant AFAIK but I would be happy to post the complete code for the activity on request, it's really basic anyway.

Answer 1

Use leading zeros for the month and day.

Answer 2

SQLite does not have built-in date fields; you have to use TEXT (textual representation), INTEGER (unix time) or REAL. See: Datatypes in SQLite.

You can use leading zeroes (as dan suggested) or better yet, save them as UNIX timestamps in INTEGER fields. You can convert these back to human-readable dates using SQLite's date and time functions or Java's (or whatever you are using).

Answer 3

I have had to do similar sorting. I ended up storing dates as REAL values in SQLite as the number of milliseconds since Epoch Jan 1 1970 00:00 GMT. You can then simply sort these numerically (larger number occurred later chronologically). You can then convert to strings using the SimpleDateFormat. Also allows you to do great things with time elements which helps-- http://developer.android.com/reference/java/text/SimpleDateFormat.html