How to get latest 7 days and order it if date format is yyyyMdd?

Question

I try to describe my code more clearly.

So here is my issue:

I'm stuck with my database , i want to search latest 7 days , but i store my date format is yyyymdd like 2017/9/30 If i want to search all of my records and order it , my succeed code like this:

public List<Contact> sortingDate() {
        List<Contact> contactList = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CONTACTS +" ORDER BY\n" +
                "    SUBSTR(Date, 1, 4) DESC,\n" +
                "    CASE WHEN INSTR(SUBSTR(Date, 6), '/') = 2\n" +
                "         THEN '0' || SUBSTR(Date, 6, 1)\n" +
                "         ELSE SUBSTR(Date, 6, 2) END DESC,\n" +
                "    CASE WHEN LENGTH(SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)) = 1\n" +
                "         THEN '0' || SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)\n" +
                "         ELSE SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1) END 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 will print:

And then i try to add search latest days code to my function, i set a default date 2017/09/30

// order latest days
    public List<Contact> latestDays() {
        List<Contact> contactList = new ArrayList<>();
        // Database time format is: yyyymdd
        Calendar mCalendar = new GregorianCalendar();
        int mYear = mCalendar.get(Calendar.YEAR);
        int mMonth = mCalendar.get(Calendar.MONTH)+1;
        int mDay = mCalendar.get(Calendar.DAY_OF_MONTH);
        String today = String.valueOf(mYear).concat(String.valueOf(mMonth).concat(String.valueOf(mDay)));
        System.out.println(today+"today");
        SQLiteDatabase db = this.getWritableDatabase();
        // WHERE date...it is what i try the code
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CONTACTS +" WHERE date > (SELECT DATETIME('2017/09/30'," + " ' -7" + " day'))" +
                " ORDER BY\n" +
                " SUBSTR(Date, 1, 4) DESC,\n" +
                " CASE WHEN INSTR(SUBSTR(Date, 6), '/') = 2\n" +
                " THEN '0' || SUBSTR(Date, 6, 1)\n" +
                " ELSE SUBSTR(Date, 6, 2) END DESC,\n" +
                " CASE WHEN LENGTH(SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)) = 1\n" +
                " THEN '0' || SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1)\n" +
                " ELSE SUBSTR(SUBSTR(Date, 6), INSTR(SUBSTR(Date, 6), '/') + 1) END 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;
    }

I call my function:

private DatabaseHandler db;
contactList = db.latestDays();
System.out.println(contactList.size());

It shows System.out: 0

So what code i miss it if i want to get latest 7 days like 2017/9/24 2017/9/29 2017/9/30 in my database ?

Any help would be appreciated. Thanks in advance.

Answer 1

This was a true learning experience and due to current changes is how dates are used in Android and Java this answer will need to be revised. The question was how to find all the dates between a 7 day range in a sqlite DB. FIRST the most important concept is how the data is formatted in the DB this format 10-18-2017 as a string has many issues while the this format 10182017 is not very readable unless you are a computer NOT! So store the dates like this 10152017 and then to display them format like this 10-18-2017 My DB design declared the date variable as TEXT Suggest testing with INTEGER not so sure it will work any better unless you make all the integer the same length 10182017 we had issues with this date 1012017 using and integer for the search pattern here is the query code enjoy

public List<DBModel> getDataFromDB(){
    //String query = "SELECT * FROM " + TABLE_INFO + " WHERE " + Col_PURCHASE_DATE + " > 0 " + " ORDER BY " + Col_ID + " DESC ";
    /* Notice the SPACES before AND after the words WHERE ORDER BY ASC or DESC most of all the condition " > 0 "*/
    /* =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=*/
   Cursor cursor = null;
    List<DBModel> modelList = new ArrayList<>();
    if(use == true){
        //String query = "SELECT * FROM " + TABLE_INFO + " WHERE " + Col_PURCHASE_DATE + " ='" + selectSTRING + "'";
        String query = "SELECT * FROM " + TABLE_INFO + " WHERE " + Col_PURCHASE_DATE + " >= " + "'" + selectSTRING + "'" + " AND " + Col_PURCHASE_DATE + " <= " + "'" + secondSTRING + "'";
        //String query = "SELECT * FROM " + TABLE_INFO + " WHERE " + Col_PURCHASE_DATE + " >= " + "'"+ 1012017 +"'" + " AND " + Col_PURCHASE_DATE + " <= " + "'"+ 10312017 +"'";
        db = this.getWritableDatabase();
        cursor = db.rawQuery(query,null);
    }
    if(use == false){
        String query = "SELECT * FROM " + TABLE_INFO;
        db = this.getWritableDatabase();
        cursor = db.rawQuery(query,null);
    }

    if (cursor.moveToFirst()){

        do {
            DBModel model = new DBModel();
            model.setRowid(cursor.getInt(0));
            model.setStation_Name(cursor.getString(1));
            model.setDate_of_Purchase(cursor.getString(2));
            model.setGas_Cost(cursor.getString(3));
            modelList.add(model);

            int sz = modelList.size();
            int out =   model.setRowid(cursor.getInt(0));
            String out1 =  model.setStation_Name(cursor.getString(1));
            String out2 =  model.setDate_of_Purchase(cursor.getString(2));
            String out3 = model.setGas_Cost(cursor.getString(3));
            System.out.println("==============getDataFromDB ID "+out);
            System.out.println("==============getDataFromDB Station "+out1);
            System.out.println("==============getDataFromDB Date "+out2);
            System.out.println("==============getDataFromDB Cost "+out3);
            System.out.println("======= ======getDataFromDB SIZE "+sz);

        }while (cursor.moveToNext());
    }
    db.close();
    cursor.close();
    return modelList;
}

Here is a method to deal with dates that are only 7 or 6 characters long

                    if(searchTo.length()==7){
                    StringBuilder str = new StringBuilder(searchTo);
                    System.out.println("string = " + str);


                    str.insert(2, '0');
                    etToDate.setText(str);
                }else if (searchFrom.length() == 6){
                    StringBuilder str = new StringBuilder(searchFrom);
                    str.insert(0,'0');
                    str.insert(2,'0');
                    etFromDate.setText(str);
                }else {
                    etToDate.setText(searchTo);
                }