sqlite delete row by timestamp < x days

Question

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?

Answer 1

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.