How to perform delete on a CURRENT_TIMESTAMP column?

Question

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!

Save the table as timestamp(Long Value : 1231413123) It will be easier for you to query the relevant values during the deletion process. — Arda Kazancı, Sep 02 '20 at 12:44
yes. Timestamp timestamp = new Timestamp(System.currentTimeMillis()); System.out.println(timestamp.getTime()); — Arda Kazancı, Sep 02 '20 at 12:48
@kzncrda timestamp is from java.sql. What I store in table is zoneddatetime [Java.time]. — Gladiator, Sep 03 '20 at 09:41

score 0 · Answer 1 · answered Sep 02 '20 at 12:50

Save the table as timestamp(Long Value : 1231413123) It will be easier for you to query the relevant values during the deletion process.

Sample Table;

CREATE TABLE MyTable(
        ID INTEGER PRIMARY KEY,
        Foo TEXT,
        Bar TEXT,
        Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    );

score 0 · Accepted Answer · answered Sep 03 '20 at 10:16

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) + "'" + ";");

How to perform delete on a CURRENT_TIMESTAMP column?

2 Answers2