Android check when table was last updated

Question

Hi there I'm starting to develop an Android app which will be using a SQLite DB that will hold several tables. What I would like to know is when specific tables were last updated as some data would not need to be updated for say a week where as other tables will need to be updated every few hours. Trying to save bandwidth and create a faster more responsive app. Not sure if there is a command for this (can't seem to find anything) or if I should add a field within each table with the current time and date and use System.currentTimeMillis() to work out how long it has been.

I know this was asked below but was never answered, any help would be awesome :)

Check when an SQLite database/table was last updated (PHP)

Answer 1

A little late, but what the hell - you can also put a bunch of TRIGGERs on your tables and maintain a modification table, like shown below. The most recent modification is logged with type and date + time in the modifications table. Creating TRIGGERs like that can be easily accomplished in a simple method & called for each table created in your SQLiteOpenHelper

CREATE TABLE table1 (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    text1 TEXT,
    text2 TEXT
);

CREATE TABLE table2 (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    text1 TEXT,
    int1 INTEGER
);

CREATE TABLE modifications (
    table_name TEXT NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
    action TEXT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER IF NOT EXISTS table1_ondelete AFTER DELETE ON table1
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table1','DELETE');
END;

CREATE TRIGGER IF NOT EXISTS table2_ondelete AFTER DELETE ON table2
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table2','DELETE');
END;

CREATE TRIGGER IF NOT EXISTS table1_onupdate AFTER UPDATE ON table1
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table1','UPDATE');
END;

CREATE TRIGGER IF NOT EXISTS table2_onupdate AFTER UPDATE ON table2
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table2','UPDATE');
END;

CREATE TRIGGER IF NOT EXISTS table1_oninsert AFTER INSERT ON table1
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table1','INSERT');
END;

CREATE TRIGGER IF NOT EXISTS table2_oninsert AFTER INSERT ON table2
BEGIN
    INSERT INTO modifications (table_name, action) VALUES ('table2','INSERT');
END;

Answer 2

That I know of there is no SQL function to do it.

You could add a field to each record with a timestamp, then do a query to return the latest timestamp from the table.