How to update the timestamp in SQLite in Android?

Question

I have a SQLite database in my Andoid App. I have three rows, ID, DATA and DATE, where DATE is filled by SQLite by using the TIMESTAMP as follows CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY AUTOINCREMENT, $DATE TEXT NOT NULL, $DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP);.

It works fine for me. But when I update the DATA row, the timestamp does not update with. I update the DATA ad follows:

fun updateData(id: String, data: String): Boolean {
    db.update(TABLE, ContentValues().apply {
        put(DATA, data)
    }, "$ID = ?", arrayOf(id))
    return true
}

After update, the timestamp stays the same. How can I update the timesamp after each DATA updating?

Read up on triggers: https://www.sqlite.org/lang_createtrigger.html — Shawn, Mar 28 '19 at 23:27
for [example](https://stackoverflow.com/a/50605044/549372) ... `DEFAULT` only applies to `INSERT`. — Martin Zeitler, Mar 28 '19 at 23:41
Could append current_timestamp to your string 'data' before running update so that you get the current timestamp each update — Vahalaru, Mar 28 '19 at 23:52

score 2 · Answer 1 · answered Mar 29 '19 at 11:06

2

Create this trigger:

CREATE TRIGGER tab_after_update AFTER UPDATE ON tab
WHEN old.data <> new.data
BEGIN
  UPDATE tab
  SET date = CURRENT_TIMESTAMP
  WHERE id = old.id; 
END;

Replace tab with your table's name.
This will be executed every time the column data is updated.
You must execute this statement in the SQLiteOpenHelper's class onCreate() method right after the CREATE statement of the table.
After you make the changes in the class, uninstall the app from the device where you test it so the database is deleted and rerun.

answered Mar 29 '19 at 11:06

forpas

160,666
10
38
76

Hey bro, probebly your solution could also help me to fix my problem. But I preffered to do it otherwise. Please see my answer. P.S. Thank you for your suggestion. – Mark Delphi Mar 29 '19 at 14:52
I thought you wanted everything to be done by the db engine. Anyway: **don't** use this format: `dd.MM.yyyy hh:mm` to store the dates. SQLite is not that flexible with dates. Store it as `YYYY-MM-DD hh:mm` which is comparable and can be formatted later to suit your needs. – forpas Mar 29 '19 at 14:56

Mark Delphi · Accepted Answer · 2019-03-29T17:10:03.123

I fixed the update by changing

override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY AUTOINCREMENT, $DATA TEXT, $DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP);")
}

to

override fun onCreate(db: SQLiteDatabase) {
    db.execSQL("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY AUTOINCREMENT, $DATA TEXT, $DATE TEXT;")
}

and then I defined a variable to pick up the current time

private var date = SimpleDateFormat("dd.MM.yyyy hh:mm").format(Date())

and added it to update mathod:

fun updateData(id: String, data: String): Boolean {
    db.update(TABLE, ContentValues().apply {
    put(DATA, data)
    put(DATE, date)
    }, "$ID = ?", arrayOf(id))
    return true
}

That's all. Now it updates the date for each change/modification.

How to update the timestamp in SQLite in Android?

2 Answers2