如何在SQLite中自动添加时间戳？

Question

我有一个SQLite版本3的数据库，使用C#创建了一个应用程序来使用这个数据库。

我想在表中使用一个时间戳字段来处理并发，但是我注意到当我插入一条新记录时，这个字段没有被设置，它是空的。

例如，在MS SQL Server中，如果我使用时间戳字段，它将由数据库更新，我不必自己设置。这在SQLite中可行吗？

- Álvaro García

---

这个回答解决了你的问题吗？sqlite数据库默认时间值'now' - user4157124

Answer 1

只需为字段声明默认值：

CREATE TABLE MyTable(
    ID INTEGER PRIMARY KEY,
    Name TEXT,
    Other STUFF,
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

不过，如果您的 INSERT 指令明确将该字段设置为 NULL，那么它将被设置为 NULL。

- CL.

---

这并不完全等同于SQL Server的时间戳，因为它是基于系统时钟的；如果您更改了时钟，该值可能会倒退。在SQL Server中，时间戳值始终是递增的。 - Rory MacLeod

SQLite没有DATETIME数据类型，但是它接受任何东西作为字段类型。 - CL.

在您提供的链接中提到了SQLite中的DATETIME数据类型。请阅读2.2亲和性名称示例。 - Rafique Mohammed

这似乎不涵盖UPDATE语句。看起来触发器是唯一的解决方法。 - Dale Anderson

抱歉，你是正确的。我错误地将你的回答和Javed的回答合并了。使用DEFAULT (STRFTIME（'%Y-%m-%d %H:%M:%f'，'NOW'，'localtime'）)可以获得有意义的微秒值。 - Dietmar

Answer 2

你可以在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');

这是结果：

SELECT * FROM my_table;

- SBotirov

---

无法添加具有非常量默认值的列（ALTER TABLE“main”。“xxx_data” ADD COLUMN“created_date” TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP） - toing_toing

在SQLite中，TIMESTAMP不是真正的数据类型。他们的类型亲和性文档使用DATETIME。 - sproketboy

Answer 3

阅读datefunc的一个自动完成日期时间的工作示例如下：

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

让我们以一种启动自动日期时间完成的方式插入几行：

sqlite> INSERT INTO 'test' ('id') VALUES (null);
sqlite> INSERT INTO 'test' ('id') VALUES (null);

存储的数据清晰地显示前两个相同，但第三个函数不同：

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

注意，SQLite函数需要用括号括起来！这个例子展示起来有多难？

玩得开心！

- centurian

Answer 4

你可以使用触发器。效果非常好。

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;

更新： 如评论中所提到的，我在版本3.44.0上测试了代码，幸运的是它可以工作！为什么呢？因为SQLite将DATETIME数据类型（来自传统的SQL实现）转换为NUMERIC数据类型，基于列亲和性的确定（参见"https://www.sqlite.org/datatype3.html"第3.1节）。

但最好使用TEXT类型而不是Datetime类型：

CREATE TABLE MyTable(
ID INTEGER PRIMARY KEY,
Name TEXT,
Other STUFF,
Timestamp TEXT);

也测试过这种方法，它有效：）。

- Rahmat Anjirabi

---

嗯，我已经分别创建了两个字段，create_date和write_date。我使用了你的代码，但是出现了“触发器递归级别过多”的错误。我不得不通过添加一个UPDATE OF子句来排除update触发器中的write_date。我希望这是正确的做法。 - ChesuCR

此外，我认为Timestamp字段应该是TEXT类型，因为SQLite中不存在DATETIME类型。 - ChesuCR

Answer 5

为了补充上面的回答...

如果您正在使用EF，请在属性上添加数据注释[Timestamp]，然后进入上下文类中的重写OnModelCreating，并添加此Fluent API代码：

modelBuilder.Entity<YourEntity>()
                .Property(b => b.Timestamp)
                .ValueGeneratedOnAddOrUpdate()
                .IsConcurrencyToken()
                .ForSqliteHasDefaultValueSql("CURRENT_TIMESTAMP");

它将为插入到此表中的每个数据设置默认值。

- Rafael

---

我找不到sqliteHasDefault所需的包。你知道我需要从NuGet获取哪个包吗？ - modmoto

@Simons0n，请尝试使用 Microsoft.EntityFrameworkCore.Metadata.Builders.PropertyBuilder 命名空间。 - Rafael

Answer 6

您可以通过使用自定义日期时间来...

 create table noteTable3 
 (created_at DATETIME DEFAULT (STRFTIME('%d-%m-%Y   %H:%M', 'NOW','localtime')),
 title text not null, myNotes text not null);

使用 'NOW'，'localtime' 获取当前系统日期，否则在数据库中插入时间后会显示一些过去或其他时间。

谢谢...

- Javed

---

如果你需要微秒级别的精度，可以使用DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW','localtime'))。仅使用DEFAULT CURRENT_TIMESTAMP将只给你一秒的粒度。 - Dietmar

Answer 7

如果您使用SQLite DB-Browser，可以按照以下方式更改默认值：

1. 选择数据库结构
2. 选择表格
3. 修改表格
4. 在您的列下面的“默认值”中放入值：=(datetime('now','localtime'))

建议在更改之前更新数据库，因为值中的错误格式可能会导致SQLLite浏览器出现问题。

- SJX

---

如果它抱怨无法添加具有给定默认值的新列，则（1）仅添加没有任何默认值的列，（2）保存，（3）再次修改表并为步骤1中创建的列输入默认值。 - Magnus