C# 如何在 SQLite 中设置自动时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14461851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to have an automatic timestamp in SQLite?
提问by álvaro García
I have an SQLite database, version 3 and I am using C# to create an application that uses this database.
我有一个 SQLite 数据库版本 3，我正在使用 C# 创建一个使用该数据库的应用程序。
I want to use a timestamp field in a table for concurrency, but I notice that when I insert a new record, this field is not set, and is null.
我想在表中使用时间戳字段进行并发，但我注意到当我插入新记录时，该字段未设置，并且为空。
For example, in MS SQL Server if I use a timestamp field this is updated by the database, I have not to set by myself. is this possible in SQLite?
例如，在 MS SQL Server 中，如果我使用由数据库更新的时间戳字段，则不必自己设置。这在 SQLite 中可能吗？
采纳答案by CL.
Just declare a default value for a field:
只需为一个字段声明一个默认值：
CREATE TABLE MyTable(
ID INTEGER PRIMARY KEY,
Name TEXT,
Other STUFF,
Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
However, if your INSERTcommand explicitly sets this field to NULL, it will be set to NULL.
但是，如果您的INSERT命令明确将此字段设置为NULL，则它将设置为NULL。
回答by mr.boyfox
You can create TIMESTAMP field in table on the SQLite, see this:
您可以在 SQLite 的表中创建 TIMESTAMP 字段，请参阅：
CREATE TABLE my_table (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(64),
sqltime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
INSERT INTO my_table(name, sqltime) VALUES('test1', '2010-05-28T15:36:56.200');
INSERT INTO my_table(name, sqltime) VALUES('test2', '2010-08-28T13:40:02.200');
INSERT INTO my_table(name) VALUES('test3');
This is the result:
这是结果：
SELECT * FROM my_table;
回答by Javed
you can use the custom datetime by using...
您可以通过使用自定义日期时间...
create table noteTable3
(created_at DATETIME DEFAULT (STRFTIME('%d-%m-%Y %H:%M', 'NOW','localtime')),
title text not null, myNotes text not null);
use 'NOW','localtime' to get the current system date else it will show some past or other time in your Database after insertion time in your db.
使用 'NOW','localtime' 获取当前系统日期，否则它会在您的数据库中插入时间后在您的数据库中显示过去或其他时间。
Thanks You...
感谢您...
回答by Rafael
To complement answers above...
为了补充上面的答案......
If you are using EF, adorn the property with Data Annotation [Timestamp], then go to the overrided OnModelCreating, inside your context class, and add this Fluent API code:
如果您使用的是 EF，请使用 Data Annotation [Timestamp] 装饰该属性，然后转到您的上下文类中被覆盖的 OnModelCreating，并添加此 Fluent API 代码：
modelBuilder.Entity<YourEntity>()
.Property(b => b.Timestamp)
.ValueGeneratedOnAddOrUpdate()
.IsConcurrencyToken()
.ForSqliteHasDefaultValueSql("CURRENT_TIMESTAMP");
It will make a default value to every data that will be insert into this table.
它将为将插入到此表中的每个数据设置一个默认值。
回答by centurian
Reading datefunca working example of automatic datetime completion would be:
sqlite> CREATE TABLE 'test' (
...> 'id' INTEGER PRIMARY KEY,
...> 'dt1' DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')),
...> 'dt2' DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime')),
...> 'dt3' DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'))
...> );
Let's insert some rows in a way that initiates automatic datetime completion:
让我们以启动自动日期时间完成的方式插入一些行：
sqlite> INSERT INTO 'test' ('id') VALUES (null);
sqlite> INSERT INTO 'test' ('id') VALUES (null);
The stored data clearly shows that the first two are the same but not the third function:
存储的数据清楚地表明前两个是相同的，但不是第三个功能：
sqlite> SELECT * FROM 'test';
1|2017-09-26 09:10:08|2017-09-26 09:10:08|2017-09-26 09:10:08.053
2|2017-09-26 09:10:56|2017-09-26 09:10:56|2017-09-26 09:10:56.894
Pay attention that SQLite functions are surrounded in parenthesis! How difficult was this to show it in one example?
注意 SQLite 函数用括号括起来！在一个例子中展示它有多难？
Have fun!
玩得开心！
回答by Rahmat Anjirabi
you can use triggers. works very well
你可以使用触发器。效果很好
CREATE TABLE MyTable(
ID INTEGER PRIMARY KEY,
Name TEXT,
Other STUFF,
Timestamp DATETIME);
CREATE TRIGGER insert_Timestamp_Trigger
AFTER INSERT ON MyTable
BEGIN
UPDATE MyTable SET Timestamp =STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') WHERE id = NEW.id;
END;
CREATE TRIGGER update_Timestamp_Trigger
AFTER UPDATE On MyTable
BEGIN
UPDATE MyTable SET Timestamp = STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') WHERE id = NEW.id;
END;
回答by SJX
If you use the SQLite DB-Browser you can change the default value in this way:
如果您使用 SQLite DB-Browser，您可以通过以下方式更改默认值：
- Choose database structure
- select the table
- modify table
- in your column put under 'default value' the value: =(datetime('now','localtime'))
- 选择数据库结构
- 选择表
- 修改表
- 在您的列中，将值放在“默认值”下：=(datetime('now','localtime'))
I recommend to make an update of your database before, because a wrong format in the value can lead to problems in the SQLLite Browser.
我建议先更新您的数据库，因为值中的错误格式可能会导致 SQLLite 浏览器出现问题。
