How to automatically set timestamp in room SQLite database?

Question

I am trying to have SQLite create automatic timestamps with CURRENT_TIMESTAMP.

I took the liberty of using Google's code:

// roomVersion = '2.2.2'
@Entity
public class Playlist {
    @PrimaryKey(autoGenerate = true)
    long playlistId;
    String name;
    @Nullable
    String description;
    @ColumnInfo(defaultValue = "normal")
    String category;
    @ColumnInfo(defaultValue = "CURRENT_TIMESTAMP")
    String createdTime;
    @ColumnInfo(defaultValue = "CURRENT_TIMESTAMP")
    String lastModifiedTime;
}

@Dao
interface PlaylistDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(playlist: Playlist): Long
}

This translates into an SQLite-Statement:

CREATE TABLE `Playlist` (
    `playlistId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    `name` TEXT, 
    `description` TEXT, 
    `category` TEXT DEFAULT 'normal', 
    `createdTime` TEXT DEFAULT CURRENT_TIMESTAMP, 
    `lastModifiedTime` TEXT DEFAULT CURRENT_TIMESTAMP
)

I did make one insert:

mDb.playListDao().insert(Playlist().apply { name = "Test 1" })

But the timestamps are always Null.

With the DB Browser for SQLite I added another entry, here I get timestamps.

How do I insert without a Null-Timestamp in room?

(Info: createdTime is also always the same as lastModifiedTime. I think this has to be done with triggers in SQLite, but that is a different problem not to be discussed here).

probably your DAO code is explicitly passing `NULL` instead of skipping column or passing `DEFAULT` — Lukasz Szozda, Dec 13 '19 at 13:55
The full code is above. I do not set the value explicitly, but the object does. But I need the field in the object to create the table in room style. So: How to not set Null ("" has the same result). — Gunnar Bernstein, Dec 13 '19 at 14:06

score 15 · Accepted Answer · answered Dec 13 '19 at 16:38

You don't need to use another class, you can use @Query as an alternative to the convenience @Insert.

as per :-

There are 4 type of statements supported in Query methods: SELECT, INSERT, UPDATE, and DELETE. Query

e.g.

@Query("INSERT INTO test_table001 (name) VALUES(:name) ")
void insert(String name);

You are also not limited to CURRENT_TIMESTAMP as the only means of getting the current timestamp you can use embedded datetime functions (as is shown below), which can store the value more efficiently and also be more flexible e.g. you could adjust the current time using modifiers such as '+7 days'.

If you consider the following :-

@Entity(tableName = "test_table001")
public class TestTable001 {

    @PrimaryKey
    Long id;
    @ColumnInfo(defaultValue = "CURRENT_TIMESTAMP")
    String dt1;
    @ColumnInfo(defaultValue = "(datetime('now'))")
    String dt2;
    @ColumnInfo(defaultValue = "(strftime('%s','now'))")
    String dt3;
    String name;
}

Note that the inefficient autogenerate = true has not been used BUT as will be shown you can still have an SQLite assigned id (note that you must use the type Long/Integer as opposed to long or int)
Also note the alternative ways of getting the current date time (the latter being more efficient as the value will ultimately be stored as an Integer (max 8 bytes) rather than a more byte hungry String).

With a Dao as :-

@Dao
public interface TestTable001Dao {

    @Insert()
    long insert(TestTable001 testTable001);

    @Query("INSERT INTO test_table001 (name) VALUES(:name) ")
    long insert(String name);

    @Query("SELECT * FROM test_table001")
    List<TestTable001> getAllTestTable001();
}

And the following to test/demonstrate :-

public class MainActivity extends AppCompatActivity {

    AppDatabase mRoomDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mRoomDB = Room.databaseBuilder(this,AppDatabase.class,"testdb")
                .allowMainThreadQueries()
                .build();

        TestTable001 tt01 = new TestTable001();
        tt01.setName("tt01");
        mRoomDB.useTestTable001().insert(tt01);
        mRoomDB.useTestTable001().insert("tt02");

        logAllTestTable001();
    }

    private void logAllTestTable001() {
        for (TestTable001 tt: mRoomDB.useTestTable001().getAllTestTable001()) {
            Log.d(
                    "TTINFO",
                    "ID = " + tt.getId() +
                            " Name = " + tt.getName() +
                            " Date1 = " + tt.getDt1() +
                            " Date2 = " + tt.getDt2() +
                            " Date3 = " + tt.getDt3());
        }
    }
}

The result is :-

2019-12-14 03:18:32.569 D/TTINFO: ID = 1 Name = tt01 Date1 = null Date2 = null Date3 = null
2019-12-14 03:18:32.569 D/TTINFO: ID = 2 Name = tt02 Date1 = 2019-12-13 16:18:32 Date2 = 2019-12-13 16:18:32 Date3 = 1576253912

Interesting approach. Requires a lot of fields in the insert query, while I currently use extended classes with just "suspend fun insert(obj: T): Long" for all tables. Also +1 for .allowMainThreadQueries, did not know that one. — Gunnar Bernstein, Dec 13 '19 at 17:39

score 1 · Answer 2 · answered Dec 13 '19 at 14:17

Found it. Did not read the manual.

You have to create a 2nd class without the auto-set fields to insert.

public class NameAndDescription {
   String name;
   String description
}

I think, this is not a good idea. If you have an autoincrement field in the DB it will get an automatically updated value when you pass 0. Likewise the default value of the timestamp should be used when passing null or "".

score 1 · Answer 3 · answered Feb 10 '20 at 14:41

I found the best solution was creating an abstract Dao that implemented the insert and update methods. I didn't get the default value to work (perhaps I was doing something wrong). Take a look at my answer here: How to implement created_at and updated_at column using Room Persistence ORM tools in android

How to automatically set timestamp in room SQLite database?

3 Answers3

Linked