Solution 1 :
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
);
Solution 2 :
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) + "'" + ";");
Problem :
ISSUE:
- I have table
[ INFOTABLE ]
with a timestamp column namedDATETIMESTAMP
[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!
Comments
Comment posted by Arda Kazancı
Save the table as timestamp(Long Value : 1231413123) It will be easier for you to query the relevant values during the deletion process.
Comment posted by Gladiator
Hi @kzncrda! This one you say uses Java.time?
Comment posted by Arda Kazancı
yes. Timestamp timestamp = new Timestamp(System.currentTimeMillis()); System.out.println(timestamp.getTime());
Comment posted by Gladiator
@kzncrda timestamp is from java.sql. What I store in table is zoneddatetime [Java.time].