-2

Am working on Android Sqlite where I try to fetch the rows which are inserted 1 hour before.But its not returning any thing,I checked by cursor.count()

I tried with the following queries

String[] columns = new String[] { KEY_ID, KEY_CONTENT1, KEY_CONTENT2,
                KEY_CONTENT3, KEY_CONTENT4, KEY_CONTENT5, KEY_CONTENT6};

cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, KEY_CONTENT3               + "< " + "DATETIME('NOW', '-1 hours')", null, null, null, null);

AND another query

String selectRow = "select *  FROM detail1 WHERE content3 < DATETIME('NOW', '-1 hours');";

Cursor cursor = sqLiteDatabase.execSQL(selectRow);

AND another query

String selectRow = "select *  FROM detail1 WHERE content3 < DATETIME('NOW', '-1 hours');";

cursor = sqLiteDatabase.rawQuery(selectRow,null);

This Query Works for me

String selectRow = "select *  FROM detail1 WHERE content3 > DATETIME('NOW', '-1 hours');";

Cursor cursor = sqLiteDatabase.execSQL(selectRow);
Abhinav Singh Maurya
  • 3,313
  • 8
  • 33
  • 51
user3082214
  • 1
  • 4
  • What exactly do you mean with "1 hour before"? Your queries return any records that were inserted *more* than one hour before. – CL. Jan 04 '14 at 09:40
  • The records which are inserted 1 hour before,My above Query doesn't return any records. – user3082214 Jan 04 '14 at 09:43
  • You are just repeating what you wrote in the question. Please edit your question to show an example of the current time, and what records should and should not be returned. – CL. Jan 04 '14 at 10:00
  • I edited Please check is it okay – user3082214 Jan 04 '14 at 10:07

1 Answers1

0
DATETIME('NOW', '-1 hours');"

This function returns time in this 2014-01-04, and in data base 2014-01-04 10:24:21 have in this format. here only 2014 is getting compared not the complete date. better approch i would suggest go with epoch

try below query which will give you difference in terms of seconds

SELECT (strftime('%s','now')/60) - (strftime('%s',content3)/60);

Yedhu Krishnan
  • 1,225
  • 15
  • 31
Sush
  • 3,864
  • 2
  • 17
  • 35