Search code examples
androidsqliteexceptionzoneddatetime

How to perform delete on a CURRENT_TIMESTAMP column?

ISSUE:

  • I have table [ INFOTABLE ] with a timestamp column named DATETIMESTAMP [INTEGER] datatype.
  • I need to delete records using this column but delete query throws error.
  • It is mainly because of the T [TIME indicator] inbetween the timestamp captured. [Eg:] 2020-01-01T17:45:31.127452+03:00[Asia/Riyadh]

SQLITE TABLE:

  String create_sql3 = "CREATE TABLE IF NOT EXISTS " + Tablename3 + "("
                    + SNO + " INTEGER NOT NULL," + ITEM + " INTEGER NOT NULL,"
                    + DATETIMESTAMP + " INTEGER DEFAULT CURRENT_TIMESTAMP )";

DELETE QUERY:

db.execSQL("DELETE FROM " + Tablename3 + " WHERE DATETIMESTAMP = " + Integer.parseInt(deltimestamp.get(y))  + ";");

EXCEPTION LOGGED:

android.database.sqlite.SQLiteException:
unrecognized token: "01T17" (code 1):,while compiling: DELETE
FROM INFOTABLE WHERE DATATIMESTAMP = 2020-01-01T17:45:31.127452+03:00[Asia/Riyadh];

Here deltimestamp is a String Arraylist which has datetimestamps as String. Appreciate help on Delete query correction for CURRENT_TIMESTAMP column!

Solution

  • Fixed the issue!

    QUERY (ERROR):

    db.execSQL("DELETE FROM " + Tablename3 + " WHERE DATETIMESTAMP = " + Integer.parseInt(deltimestamp.get(y))  + ";");

    QUERY (FIXED):

    db.execSQL("DELETE FROM " + Tablename3 + " WHERE DATETIMESTAMP = " + "'" + deltimestamp.get(y)  + "'" + ";");

    OR

    db.execSQL("DELETE FROM " + Tablename3 + " WHERE DATETIMESTAMP = "+ "'" + cursor.getString(7) + "'" + ";");