CODEWITHSUNDEEP
androidsql
user3332060
user3332060

Reputation: 19

how to get last seven dates data from database using current date

I want to get last 7 dates of data from current date I am storing current date as string now how I will get last 7 dates of data using query? currently my code return my last 20 data from descending order now I want to show only last 7 days data so what will I do?

  public class JobSchema extends BackBaseActivity {

            List<JobSchmeModel> jobarr, missedarr;

               @Override
protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.jobsschema);

             Calendar c = Calendar.getInstance();
     SimpleDateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm");
        String formattedDate = df.format(c.getTime());



            DatabaseHandler db = new DatabaseHandler(JobSchema.this);
                jobarr = db.getALLCompJobs();




      public class DatabaseHandler extends SQLiteOpenHelper {



               String CREATE_COMPLETED_TABLE = "CREATE TABLE " + TABLE_COMPLETED_JOBS
            + "(" + KEY_COMPID + " INTEGER PRIMARY KEY," + KEY_TIMEJOB
            + " TEXT," + KEY_PICK + " TEXT," + KEY_DEST + " TEXT,"
            + KEY_FARE + " TEXT" + ")";




                    public List<JobSchmeModel> getALLCompJobs() {
    List<JobSchmeModel> compjobsList = new ArrayList<JobSchmeModel>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_COMPLETED_JOBS+ " ORDER BY 
     "+KEY_COMPID+" DESC LIMIT 20";

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            JobSchmeModel jobmodel = new JobSchmeModel();
            jobmodel.setTime(cursor.getString(1));
            jobmodel.setPick(cursor.getString(2));
            jobmodel.setDestination(cursor.getString(3));
            jobmodel.setFare(cursor.getString(4));
            // Adding contact to list
            compjobsList.add(jobmodel);
        } while (cursor.moveToNext());
    }

    db.close();

    return compjobsList;
}

Upvotes: 0

Views: 689

Answers (1)

Chirag Ghori
Chirag Ghori

Reputation: 4231

Try this to get Date before 7 days.

Calendar calender = Calendar.getInstance();
calender.add(Calendar.DAY_OF_MONTH, -7);
Date date = new Date();
date.setTime(calender.getTimeInMillis());
SimpleDateFormat postFormater = new SimpleDateFormat("dd/MM/yy HH:mm");
String dateBefore7day =  postFormater.format(date);

Now your query will be 

String selectQuery = "SELECT  * FROM " + TABLE_COMPLETED_JOBS + " WHERE " + KEY_TIMEJOB + ">=" + dateBefore7day + " ORDER BY "+KEY_COMPID+" DESC";

Edit:

For get data between last 7 days to today and if you have future date data (data with date after current date) then you need to use this.

String selectQuery = "SELECT  * FROM " + TABLE_COMPLETED_JOBS + " WHERE " + KEY_TIMEJOB + ">=" + dateBefore7day + " AND " + KEY_TIMEJOB + "<=" + currentDate + " ORDER BY "+KEY_COMPID+" DESC";

Upvotes: 2

Related Questions