如何在会议室SQLite数据库中自动设置时间戳? [英] How to automatically set timestamp in room SQLite database?

问题描述

我正在尝试让SQLite使用CURRENT_TIMESTAMP创建自动时间戳.

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

我自由使用了Google的代码:

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
}

这将转换为SQLite语句:

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
)

我确实做了一个插入:

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

但是时间戳总是空的.

使用用于SQLite的数据库浏览器，我添加了另一个条目，在这里我获得了时间戳.

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

如何在没有Null-Timestamp的情况下插入房间?

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

(信息:createdTime也总是与lastModifiedTime相同.我认为这必须使用SQLite中的触发器来完成，但这是一个不同的问题，这里不再讨论).

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

推荐答案

您不需要使用其他类，可以将@Query用作便捷性@Insert的替代方法.

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

按照:-

查询方法支持4种类型的语句:SELECT，INSERT，UPDATE和DELETE. 查询

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

例如

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

您也不限于CURRENT_TIMESTAMP作为获取当前时间戳的唯一方法，您可以使用嵌入式datetime函数(如下所示)，该函数可以更有效地存储值，并且更加灵活，例如您可以使用诸如"+7天"之类的修饰符来调整当前时间.

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;
}

• 请注意，尚未使用效率低下的autogenerate = true，但是您仍然可以为SQLite分配ID (请注意，必须使用Long/Integer类型，而不是long或int) em>
• 还请注意获取当前日期时间的另一种方法(后者效率更高，因为该值最终将被存储为Integer(最多8个字节)，而不是更多的字节饥饿的String).
• 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).

道为:-

@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());
        }
    }
}

结果是:-

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

这篇关于如何在会议室SQLite数据库中自动设置时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！