CODEWITHSUNDEEP
androidsqlite
Shihab Uddin
Shihab Uddin

Reputation: 6931

how to select/delete all rows except within 24 hours before from current time in sqlite

i have a table named daily_mood and it has three (3) columns, looks like below:

CREATE TABLE daily_mood (moodid INTEGER PRIMARY KEY autoincrement,mood TEXT,mooddate DATETIME)

enter image description here

it's data looks like that: enter image description here

Now, i want to select/delete all rows except from current date to before 24 hours exactly according to mooddate column.

i tried this query like:

SELECT *from daily_mood where mooddate>=datetime('now', '-1 day');

the result should be 1 row(last one) from above data.but here comes 2.likeenter image description here

results should be the last row. Anyone can help me? thanks.

Upvotes: 1

Views: 3175

Answers (2)

Simon
Simon

Reputation: 14472

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);
Calendar c = Calendar.getInstance();
c.setTimeMillis(System.currentTimeMillis());
c.add(Calendar.HOUR_OF_DAY, -24);


String query = "SELECT * from daily_mood WHERE mooddate >= " + "'" + dateFormat.format(c.getTime()) + "'";

Upvotes: 4

Dinesh Sharma
Dinesh Sharma

Reputation: 21

SimpleDateFormat  df = new SimpleDateFormat("yyyy-MM-dd",Locale.US);
        Calendar cd = Calendar.getInstance();
        Calendar removeBefore = Calendar.getInstance();
        removeBefore.set(cd.get(Calendar.YEAR), cd.get(Calendar.MONTH),(cd.get(Calendar.DAY_OF_MONTH) - 7));//Here set day before old data to be removed.

try{

  sqlDatabase=getSqlWritable();
            rowaffeted=sqlDatabase.delete(TB3, TB3_Date +" < ? ", new String[]{df.format(removeBefore.getTime())});


        }catch(Exception e){
            e.printStackTrace();
        }

//you need to use following method

public SQLiteDatabase getSqlWritable(){
        if(dbhelper == null)
            dbhelper=new DbOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
        return dbhelper.getWritableDatabase();
    }

Upvotes: 1

Related Questions