Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to automatically set timestamp in room SQLite database?

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).

screenshot playlist db entries

like image 798
Gunnar Bernstein Avatar asked Dec 13 '19 13:12

Gunnar Bernstein


People also ask

Does SQLite support timestamp?

SQLite does not support built-in DateTime storage a class, but SQLite allows us to work with timestamp types.

Why is room better than SQLite?

Room is now considered as a better approach for data persistence than SQLiteDatabase. It makes it easier to work with SQLiteDatabase objects in your app, decreasing the amount of boilerplate code and verifying SQL queries at compile time.

How can I check if data is deleted in a room database?

developer.android.com/training/data-storage/room/… The URL suggests that you can return an Int value from the delete method but it returns the number of rows it has deleted in that operation. But other than that, you can query the DB once again for the record you are looking to confirm whether its deleted or not.

What is room database in Android Kotlin?

Room allows you to create tables via an Entity. Let's do this now. Create a new Kotlin class file called Word containing the Word data class. This class will describe the Entity (which represents the SQLite table) for your words. Each property in the class represents a column in the table.


3 Answers

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
like image 126
MikeT Avatar answered Oct 20 '22 10:10

MikeT


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 "".

like image 28
Gunnar Bernstein Avatar answered Oct 20 '22 09:10

Gunnar Bernstein


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

like image 1
Dagmar Avatar answered Oct 20 '22 08:10

Dagmar