1

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.

Shihab Uddin
  • 6,699
  • 2
  • 59
  • 74
  • Not an sqllite developer but I would suggest you view the results of SELECT datetime('now', '-1 day'); and verify it is what you'd expect. – jtimperley Sep 22 '12 at 05:27
  • Are you saying the date outputted isn't what you'd expect? Just to be clear, I was recommending a way of debugging, not a fix for your problem. – jtimperley Sep 22 '12 at 05:37
  • What happens when you execute the query that you've posted? What is wrong with it? – jmilloy Sep 22 '12 at 05:40
  • @jmilloy: i need the data which is exactly within 24 hours from the current time. the results come 2 rows in above. but it should be the last 1. it just calculate a whole day from current but i need within 24 hours exact. so final result should be the last row in above pic. – Shihab Uddin Sep 22 '12 at 05:53
  • 1
    I see. You need to compare timestamps and not dates. There is a detailed explanation here that can help you: http://stackoverflow.com/questions/2930768/how-to-compare-sqlite-timestamp-values – jmilloy Sep 22 '12 at 06:16

2 Answers2

4
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()) + "'";
Rensodarwin
  • 286
  • 4
  • 12
Simon
  • 14,407
  • 8
  • 46
  • 61
1
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();
    }
Dinesh Sharma
  • 21
  • 2