Implementation of the function of automatically inserting creation time and update time for sqlite3

Recently, when recording some simple structured log information, I used the SQLite3 database (save information is relatively simple, useMysql，SQL Server，PostgresThese databases are a bit of a big deal).

When developing a system in the past,MysqlandPostgresThere are quite a lot of contacts with sqlite3. This time I use it, I hope sqlite3 can also provide several basic functions, such as:

Primary key IDSelf-increasing
Automatically update when data is insertedCreation time（created_at）
Automatically update when data is updatedUpdate time（updated_at）

The process of investigating these functions is recorded as follows.

1. Prepare

First, create a database, and the sqlite3 database is actually a file.

$   
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite>

No need to worry about it hereThe file does not exist, and if it does not exist, it will be automatically created.

Create a tableposition_info, This is the table I use to record account net value and profit. The function of the fields is not to worry about, I just need to pay attention to it.id，created_at，updated_atThree fields are enough.

sqlite> CREATE TABLE IF NOT EXISTS position_info (
(x1...>     id INTEGER NOT NULL PRIMARY KEY,
(x1...>     equity REAL NOT NULL,
(x1...>     profit_loss REAL NOT NULL,
(x1...>     created_at TEXT NOT NULL,
(x1...>     updated_at TEXT NOT NULL
(x1...> );

After creation,sqlite3Check the commandposition_infoWhether the table is created.

sqlite> .tables
position_info

sqlite3The built-in commands are allPoint number（.) begins with.

After the table is created in the default way, it is found that inserting a piece of data is troublesome and it needs to be specified.position_infoAll 5 fields in the table can be inserted successfully.

sqlite> INSERT INTO position_info(id, equity,
(x1...>  profit_loss, created_at, updated_at)
   ...>  VALUES(1, 10, 2,
(x1...>   "2024-06-09 10:10:10", "2024-06-09 10:10:10");

sqlite> .headers on

sqlite> SELECT * FROM position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10

Actually, what I want to achieve is to insert and update, only focus onequityandprofit_lossTwo fields, the other 3 fields are automatically managed by the database.

similar:INSERT INTO position_info(equity, profit_loss) VALUES(10, 2);

The transformation begins below.

2. Primary key ID increases automatically

First, letPrimary key IDCan grow automatically.

sqlite> drop table position_info;
sqlite> CREATE TABLE IF NOT EXISTS position_info (
(x1...>     id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
(x1...>         equity REAL NOT NULL,
(x1...>         profit_loss REAL NOT NULL,
(x1...>     created_at TEXT NOT NULL,
(x1...>     updated_at TEXT NOT NULL
(x1...> );
sqlite> select * from position_info;
sqlite>

Delete the createdposition_info, then recreateposition_infoTable, specified when creatingid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT。

After the creation is completed, two pieces of data are inserted, and the ID field is not specified when inserting. When it is found, the database will help us insert the ID automatically.

sqlite> INSERT INTO position_info(equity,
(x1...>  profit_loss, created_at, updated_at)
   ...>  VALUES(10, 2,
(x1...>   "2024-06-09 10:10:10", "2024-06-09 10:10:10");

sqlite> INSERT INTO position_info(equity,
(x1...>  profit_loss, created_at, updated_at)
   ...>  VALUES(100, 20,
(x1...>   "2024-06-09 11:11:11", "2024-06-09 11:11:11");

sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 10:10:10|2024-06-09 10:10:10
2|100.0|20.0|2024-06-09 11:11:11|2024-06-09 11:11:11

3. Creation time (created_at)

Next, setCreation time（created_at)andUpdate time（updated_at) Automatic insertion:DEFAULT (DATETIME('now', 'localtime'))

sqlite> drop table position_info;
sqlite> CREATE TABLE IF NOT EXISTS position_info (
(x1...>     id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
(x1...>         equity REAL NOT NULL,
(x1...>         profit_loss REAL NOT NULL,
(x1...>     created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
(x1...>     updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
(x1...> );

Then insert two pieces of test data:

sqlite> INSERT INTO position_info(equity, profit_loss)
   ...>  VALUES(10, 2);
sqlite>
sqlite> INSERT INTO position_info(equity, profit_loss)
   ...>  VALUES(100, 20);

sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

Now, we just need to pay attentionequityandprofit_lossThat's it.

4. Update time (updated_at)

After the above modification, there is no problem with inserting data, but there is still a flaw when updating the data.
When updating data, the updated_at field has not changed, and it is always the time when the data is inserted.

Before update:

sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|10.0|2.0|2024-06-09 16:40:52|2024-06-09 16:40:52
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

Update the first piece of data:

sqlite> UPDATE position_info SET equity=500, profit_loss=100
   ...> WHERE id = 1;
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|500.0|100.0|2024-06-09 16:40:52|2024-06-09 16:40:52
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

The first dataequityandprofit_lossAlthough the update was successful, itupdated_atNo update, or when it was inserted2024-06-09 16:40:52。

To makeupdated_atIt can also be updated automatically, and a listener is required. When the data is updated, the data will be updated.updated_atField.

sqlite> CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
   ...> BEGIN
   ...>     UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == ;
   ...> END;

Update the data again and see:

sqlite> UPDATE position_info SET equity=1000, profit_loss=300
   ...> WHERE id = 1;
   
sqlite> select * from position_info;
id|equity|profit_loss|created_at|updated_at
1|1000.0|300.0|2024-06-09 16:40:52|2024-06-09 16:49:28
2|100.0|20.0|2024-06-09 16:40:53|2024-06-09 16:40:53

When updating data,updated_atUpdated, becoming2024-06-09 16:49:28,andcreated_atNot the same anymore.

5. Summary

Finally, create aSelf-increasing ID, automatically insertCreation timeandUpdate timeCompleteSQLas follows:

CREATE TABLE IF NOT EXISTS position_info (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    equity REAL NOT NULL,
    profit_loss REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE TRIGGER IF NOT EXISTS trigger_position_info_updated_at AFTER UPDATE ON position_info
BEGIN
    UPDATE position_info SET updated_at = DATETIME('now', 'localtime') WHERE rowid == ;
END;

The above is the detailed content of the function of automatically inserting creation time and update time for SQLite3. For more information about automatic creation and update time for SQLite3, please pay attention to my other related articles!