CODEWITHSUNDEEP
sqlitedatetimedelete-row
ppetree
ppetree

Reputation: 816

sqlite delete row by timestamp < x days

My table looks like this:

 tx.executeSql(
    "CREATE TABLE IF NOT EXISTS mytable (
      msg_id UNSIGNED INT PRIMARY KEY ASC, 
      msg_when DATETIME, 
      msg_read INTEGER, 
      title TEXT, msg_text TEXT
     )",

I add records using this query:

 tx.executeSql(
   "INSERT OR REPLACE INTO mytable
    (msg_id, msg_when, msg_read, title, msg_text)
    VALUES 
    (?,?,?,?,?)",

I want to delete rows that are over 'x' days old and I'm using this query but nothing gets deleted, the success callback is being called with no rows effected.

 tx.executeSql(
   "DELETE FROM mytable 
    WHERE msg_when <= datetime('now', '-? days')", [days],

I've also tried:

var strQuery = "DELETE FROM mytable 
   WHERE msg_when <= datetime('now', '-" +days +" days')";
tx.executeSql(strQuery,

and:

var strQuery = "DELETE FROM mytable 
  WHERE msg_when <= datetime('now', '-4 days')";
tx.executeSql(strQuery,

The data looks like:

json: {"status":"OK"
 ,"count":1
 ,"msg_id":"94"
 ,"when":"2016-08-23 11:21:01"
 ,"alerts":[
   {"msg_id":"44"
    ,"title":"Medical Emergency"
    ,"text":"Heart Attack"
    ,"msg_when":"2016-08-05 14:52:03"
   }
  ]
 }

Other than deleting rows by date, everything is working perfectly fine. Any suggestions on what I can do to fix the delete?

Upvotes: 1

Views: 4879

Answers (3)

Rahul Sood
Rahul Sood

Reputation: 109

DELETE FROM AIS WHERE timpstamp < datetime('now', '-1 minutes')

this should work, if not then check the table where timestamp is saved, check the format of the time string, it should be YYYY-MM-DD HH:MM:SS , if your forfamt doesnt match, the command will not work.

Save the timestamp using SQLite, not using system command.

Upvotes: 0

ppetree
ppetree

Reputation: 816

Ok, there's actually two tricks to solving this.

First, if you declare your field as a datetime (which is nothing more than an integer) you will NEVER get this to work. Not under iOS 9.x, not under Android and not on Chrome 52.0.2743.116 m (64-bit) on Windows 10.

What you have to do is store your date as a TEXT field and store your datetime as a string like this:

 tx.executeSql("CREATE TABLE IF NOT EXISTS mytable (msg_id UNSIGNED INT PRIMARY KEY ASC, msg_when TEXT, msg_read INTEGER, title TEXT, msg_text TEXT)",

Next, you have to wrap your comparison in (parenthesis) so that sqlite treats it as a formula and does the math conversions for you... like this:

tx.executeSql(DELETE FROM mytable WHERE (msg_when <= datetime('now', '-4 days'))",

Notice the parenthesis before the (msg_when column and after the closing parenthesis on the datetime)) function.

Upvotes: 1

cartant
cartant

Reputation: 58420

The problem with the DELETE statement is that the parameter template (?) is within the string literal and is therefore ignored. You need to use the concatenation operator (||):

DELETE FROM mytable WHERE msg_when <= datetime('now', '-' || ? || ' days')

Upvotes: 2

Related Questions