Have an issue when ORDER BY Date DESC

Question

I create a android sql database , every thing is fine at beginning.

But i find an issue that is someone enter a single day like 2017/9/8 not 2017/9/08 , my order by date desc is not working.

Here is my table , i want to sort the row by Date:

It is no working when the day is 2017/8/7 and 2017/9/7

Here is my sort date code: //Sort date : Latest to Old date

public List<Contact> sortingDate() {
    List<Contact> contactList = new ArrayList<>();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CONTACTS +" ORDER BY " +"Date DESC",null);
    //looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Contact contact = new Contact();
            contact.setID(Integer.parseInt(cursor.getString(0)));
            contact.setDate(cursor.getString(1));
            contact.setBeforeMorning(cursor.getString(2));
            contact.setAfterMorning(cursor.getString(3));
            contact.setBeforeNoon(cursor.getString(4));
            contact.setAfterNoon(cursor.getString(5));
            contact.setBeforeNight(cursor.getString(6));
            contact.setAfterNight(cursor.getString(7));
            System.out.println("The result is :" + cursor.getString(1));
            //Adding contact to list
            contactList.add(contact);
        } while (cursor.moveToNext());
    }

    return contactList;
}

It looks like when the day number is single will cause the issue, i try to order by orthers , they are no working , i don't know how to figure it out.

Any help would be appreciated . Thanks in advance.

I create the database, TEXT is date type:

@Override
public void onCreate(SQLiteDatabase db) {
    System.out.println("table is here");
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE + " TEXT,"
            + KEY_BMORNING + " TEXT," + KEY_AMORNING + " TEXT,"
            + KEY_BNOON + " TEXT," + KEY_ANOON + " TEXT,"
            + KEY_BNIGHT + " TEXT," + KEY_ANIGHT + " TEXT" +
            ")";
    db.execSQL(CREATE_CONTACTS_TABLE);

}

Answer 1

Warning: The SQLite query you are about to see is ugly and should not be repeated at home, unless you totally messed up your date format, in which case you might have no other choice.

We can try doing an ORDER BY which builds out the correct two digit month and day for each date. Note that in the case of your data, this necessitates padding single digit months and days with zero.

SELECT *
FROM contacts
ORDER BY
    SUBSTR(Date, 1, 4) DESC,
    CASE WHEN INSTR(SUBSTR(Date, 6), '/') = 2
         THEN '0' || SUBSTR(Date, 6, 1)
         ELSE SUBSTR(Date, 6, 2) END DESC,
    CASE WHEN LENGTH(SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)) = 1
         THEN '0' || SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)
         ELSE SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1) END DESC;

Note that the correct long term solution would be for you to store all months and days as two digit numbers, padding with zero on the left in the case of single digits (your years would most likely always be 4 digits).

Here is a link to a demo which demonstrates the logic of the above query. Note that I created it using MySQL, because SQLite is not supported, but other than having to replace || with CONCAT, the query is identical.

Demo

Here is the output which shows the correct order along with the year, month, and day components correctly being extracted:

Answer 2

i think because you are sorting by a string value...

try something like ORDER BY CAST(Date as DATE) DESC

Answer 3

Fetch all the data without any ordering and then perform ordering on the List using custom comparator

class DateComparator implements Comparator<Contact>
{
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
    public int compare(Contact lhs, Contact rhs)
    {
        return dateFormat.parse(lhs.getDate()).compareTo(dateFormat.parse(rhs.getDate()));
    }
}

Use above comparator to order by date

List<Contact> contactList = new ArrayList<>();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CONTACTS,null);
    //looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Contact contact = new Contact();
            contact.setID(Integer.parseInt(cursor.getString(0)));
            contact.setDate(cursor.getString(1));
            contact.setBeforeMorning(cursor.getString(2));
            contact.setAfterMorning(cursor.getString(3));
            contact.setBeforeNoon(cursor.getString(4));
            contact.setAfterNoon(cursor.getString(5));
            contact.setBeforeNight(cursor.getString(6));
            contact.setAfterNight(cursor.getString(7));
            System.out.println("The result is :" + cursor.getString(1));
            //Adding contact to list
            contactList.add(contact);
        } while (cursor.moveToNext());
    }

Collections.sort(contactList, new DateComparator());