CODEWITHSUNDEEP
androidsqlsqlite
Co Koder
Co Koder

Reputation: 2221

Deleting records in SQL

I use a string comparison on the date format to delete records on my database. I want to delete the past 5 minutes data. Here's the code that I am using. It seems to be not working. It does not delete any records. Why ? 

     int rangeInMinutes = -5;
     DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                Calendar now = Calendar.getInstance();
                Calendar range = Calendar.getInstance();

                range.add(Calendar.MINUTE, rangeInMinutes);

  queryString.append(range);
 StringBuilder queryString = new StringBuilder("date("'"+now +"'") > date("'"+range+"'");
int c = mydatabase.delete("mytable",  queryString.toString(), null)

The logcat shows: 0 record is deleted...

UPDATE:

After converting the date field from Text to Long in SQLite.

myddatabase.delete("mytable", "date > datetime('now','-5 minutes') AND date < datetime('now') ", null);

Still It does not delete any records. Why ?

Upvotes: 0

Views: 168

Answers (2)

Stewart
Stewart

Reputation: 496

Have a look at sqlite date function

Try this:

myddatabase.delete("mytable", "dateCol > datetime('now','-5 minutes') AND dateCol < datetime('now') ", null);

where dateCol is the name of your date column

Edit:

Here is the output for testing the where clause above:

 sqlite> create table test (_id int, testdate string);
create table test (_id int, testdate string);
sqlite> insert into test values(1, datetime('now'));
insert into test values(1, datetime('now'));
sqlite> insert into test values(2, datetime('now'));
insert into test values(2, datetime('now'));
sqlite> insert into test values(3, datetime('now'));
insert into test values(3, datetime('now'));
sqlite> insert into test values(4, datetime('now', '-10 minutes'));
insert into test values(4, datetime('now', '-10 minutes'));
sqlite> select * from test;
select * from test;
1|2013-07-25 21:01:05
2|2013-07-25 21:01:07
3|2013-07-25 21:01:09
4|2013-07-25 20:51:12
sqlite> select * from test where testdate < datetime('now') and testdate > datet
ime('now', '-5 minutes');
select * from test where testdate < datetime('now') and testdate > datetime('now
', '-5 minutes');
1|2013-07-25 21:01:05
2|2013-07-25 21:01:07
3|2013-07-25 21:01:09
sqlite> delete from test where testdate < datetime('now') and testdate > datetim
e('now', '-5 minutes');
delete from test where testdate < datetime('now') and testdate > datetime('now',
 '-5 minutes');
sqlite> select * from test;
select * from test;
4|2013-07-25 20:51:12
sqlite>

row with _id 4 is excluded from the delete as it 10 minutes in the past and therefore is the only remaining row.

Upvotes: 1

Roy Hinkley
Roy Hinkley

Reputation: 10641

For starters, it appears to you are attempting to perform a math operation on a string - which will not result in what you think it will - thus your current predicament.

You would be much better off on almost every aspect of what you are trying to accomplish using time in milliseconds: performance, simplicity and allow the native db engine to do the work for you. By storing as a string, you prevent the db engine from doing any sort of compare that you would normally do with dates. Second, storing as a string is a terrible waste of space, which Android has a limited amount of on the phone to begin with.

You will need a long not an integer if you work with the time value in code - you will surely overflow your buffer if you try to push a time value in milliseconds into an integer.

Upvotes: 1

Related Questions