Deleting records in SQL

Question

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...

I store the dates in a String format.

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 ?

In your code you use rangeInDays not rangeInMinutes, in the range.add is that correct? — Stewart
– Stewart, Commented Jul 25, 2013 at 18:32
I store the dates in a String format. Why not use the system unit of milliseconds and store as integer? It will make comparisons much easier. — Simon
– Simon, Commented Jul 25, 2013 at 18:33
@Simon, if i do not find a solution, i will do that. But, what is wrong with the code that I uploaded ? — Co Koder
– Co Koder, Commented Jul 25, 2013 at 18:57
Is date considered a reserved word? try [date] in comparisons — basdwarf
– basdwarf, Commented Jul 25, 2013 at 19:00

Stewart · Accepted Answer · 2013-07-25 22:08:44Z

1

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.

edited Jul 25, 2013 at 22:08

answered Jul 25, 2013 at 19:01

Stewart

4963 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Co Koder Over a year ago

it gives an error: The method delete(String, String, String[]) in the type SQLiteDatabase is not applicable for the arguments (String, String, String)

Stewart Over a year ago

Sorry the last parameter should be an String Array, I am not near my java IDE at the moment

Stewart Over a year ago

You could just set the last parameter to null and replace the ? with -5

Co Koder Over a year ago

it is ok, What about 'now', is it recognized by sqlite or i define it?

Stewart Over a year ago

I have updated it to datetime('now') after checking the documentation I linked, again. Have a look at it, it explains in detail. It basically returns the current date time.

|

Roy Hinkley · Accepted Answer · 2013-07-25 19:19:05Z

1

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.

edited Jul 25, 2013 at 19:19

answered Jul 25, 2013 at 18:57

Roy Hinkley

10.7k21 gold badges87 silver badges128 bronze badges

2 Comments

Simon Over a year ago

I was right in every respect ;) SQLite stores numerics very differently. It does not have a long data type. It will store a long as an integer of 8 bytes. sqlite.org/datatype3.html

Roy Hinkley Over a year ago

@Simon, sorry I was referring to the Java datatypes, not sqlite.

Collectives™ on Stack Overflow

Deleting records in SQL

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related