How to have an automatic timestamp in SQLite?

In SQLite, setting automatic timestamps typically involves utilizing SQLite's default value feature, specifically the CURRENT_TIMESTAMP function. This function automatically generates the current date and time upon record insertion. The following are the specific implementation methods:

1. Creating the Table with Default Values

When creating a new table, you can specify the default value CURRENT_TIMESTAMP for the datetime column. This way, whenever a new record is inserted, if no value is provided for this column, SQLite automatically uses the current date and time.

For example, if you want to create a table to record user activities, you can define it as:

sql
CREATE TABLE user_activity (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    activity TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example, the created_at column is set to automatically use the current timestamp when new records are created.

2. Using the DEFAULT Keyword When Inserting Records

When inserting records, you can explicitly use the DEFAULT keyword to trigger the default value, although it is typically unnecessary because SQLite automatically applies the default value if no column value is provided during insertion.

sql
INSERT INTO user_activity (username, activity, created_at) VALUES ('JohnDoe', 'login', DEFAULT);

This will use the default timestamp for the created_at column.

3. Using CURRENT_TIMESTAMP When Updating Existing Records

If you need to set the timestamp when updating records, you can explicitly use CURRENT_TIMESTAMP in the update command.

sql
UPDATE user_activity SET activity='logout', created_at=CURRENT_TIMESTAMP WHERE id=1;

This way, not only during insertion, but also during update operations, the correct timestamp is set.

Summary

By using DEFAULT CURRENT_TIMESTAMP in table definitions and CURRENT_TIMESTAMP in SQL operations when needed, you can effectively manage timestamps in SQLite databases, ensuring the temporal accuracy and reliability of your data. This method is simple, efficient, and highly suitable for applications requiring automatic time recording.