blocks|key|176705|text|只需为字段声明一个默认值：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|176706|CREATE+TABLE+MyTable(
++++ID+INTEGER+PRIMARY+KEY,
++++Name+TEXT,
++++Other+STUFF,
++++Timestamp+DATETIME+DEFAULT+CURRENT_TIMESTAMP
);|code-block|syntax|javascript|176707|但是，如果您的INSERT命令将此字段显式设置为NULL，则它将被设置为NULL。|offset|length|style|CODE|176708|entityMap^0|0|0|7|6|O|4|10|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

Just declare a default value for a field:

<pre><code>CREATE TABLE MyTable(
 ID INTEGER PRIMARY KEY,
 Name TEXT,
 Other STUFF,
 Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
</code></pre>

However, if your <code>INSERT</code> command explicitly sets this field to <code>NULL</code>, it will be set to <code>NULL</code>.

blocks|key|394643|text|您可以在SQLite上的表中创建时间戳字段，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|394644|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');|code-block|syntax|javascript|394645|这就是结果：|394646|SELECT+*+FROM+my_table;|394647|​|394648|📷|atomic|offset|length|394649|394650|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/3861da4265c43579a2f8d4a48d3cacd7.jpeg|imageAlt^0|0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|13|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|15|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|16|8|@]|9|@$P|17|Q|18|1|19]]|A|$]]|$1|R|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z|10|-4]]]]

You can create TIMESTAMP field in table on the SQLite, see this:

<pre><code>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');
</code></pre>

This is the result:

<pre><code>SELECT * FROM my_table;
</code></pre>

<img src="https://i.stack.imgur.com/KbQwn.jpg" alt="enter image description here">

blocks|key|176815|text|您可以使用自定义datetime，方法是...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|176816|+create+table+noteTable3+
+(created_at+DATETIME+DEFAULT+(STRFTIME('%25d-%25m-%25Y+++%25H:%25M',+'NOW','localtime')),
+title+text+not+null,+myNotes+text+not+null);|code-block|syntax|javascript|176817|使用'NOW'，'localtime‘来获取当前系统日期，否则它将在数据库中插入时间之后显示数据库中的过去或其他时间。|176818|谢谢你。|176819|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

you can use the custom datetime by using...

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

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.

Thanks You...

blocks|key|176847|text|为了补充上面的答案...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|176848|如果你使用EF，用Data+Annotation+Timestamp装饰这个属性，然后转到你的context类中被覆盖的OnModelCreating，并添加这个流畅的API代码：|176849|modelBuilder.Entity<YourEntity>()
++++++++++++++++.Property(b+=>+b.Timestamp)
++++++++++++++++.ValueGeneratedOnAddOrUpdate()
++++++++++++++++.IsConcurrencyToken()
++++++++++++++++.ForSqliteHasDefaultValueSql("CURRENT_TIMESTAMP");|code-block|syntax|javascript|176850|它将为将要插入到该表中每个数据设置一个默认值。|176851|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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: 

<pre><code>modelBuilder.Entity&lt;YourEntity&gt;()
 .Property(b =&gt; b.Timestamp)
 .ValueGeneratedOnAddOrUpdate()
 .IsConcurrencyToken()
 .ForSqliteHasDefaultValueSql("CURRENT_TIMESTAMP");
</code></pre>

It will make a default value to every data that will be insert into this table.

blocks|key|394742|text|读取datefunc自动日期时间补全的工作示例如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|394743|sqlite>+CREATE+TABLE+'test'+(+
+++...>++++'id'+INTEGER+PRIMARY+KEY,
+++...>++++'dt1'+DATETIME+NOT+NULL+DEFAULT+(datetime(CURRENT_TIMESTAMP,+'localtime')),+
+++...>++++'dt2'+DATETIME+NOT+NULL+DEFAULT+(strftime('%25Y-%25m-%25d+%25H:%25M:%25S',+'now',+'localtime')),+
+++...>++++'dt3'+DATETIME+NOT+NULL+DEFAULT+(strftime('%25Y-%25m-%25d+%25H:%25M:%25f',+'now',+'localtime'))
+++...>+);|code-block|syntax|javascript|394744|让我们以一种启动自动日期时间完成的方式插入一些行：|394745|sqlite>+INSERT+INTO+'test'+('id')+VALUES+(null);
sqlite>+INSERT+INTO+'test'+('id')+VALUES+(null);|394746|存储的数据清楚地表明前两个函数是相同的，但不是第三个函数：|394747|sqlite>+SELECT+*+FROM+'test';
1%7C2017-09-26+09:10:08%7C2017-09-26+09:10:08%7C2017-09-26+09:10:08.053
2%7C2017-09-26+09:10:56%7C2017-09-26+09:10:56%7C2017-09-26+09:10:56.894|394748|请注意，SQLite函数被括在括号中！在一个例子中展示它有多难？|394749|玩得开心!|394750|entityMap|0|LINK|mutability|MUTABLE|url|https://sqlite.org/lang_datefunc.html^0|2|8|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|17|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|18|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|C|$]]|$1|O|3|P|5|F|7|1A|8|@]|9|@]|C|$G|H]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|C|$]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]]]

Reading <a href="https://sqlite.org/lang_datefunc.html" rel="noreferrer">datefunc</a> a working example of automatic datetime completion would be:

<pre><code>sqlite&gt; CREATE TABLE 'test' ( 
 ...&gt; 'id' INTEGER PRIMARY KEY,
 ...&gt; 'dt1' DATETIME NOT NULL DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')), 
 ...&gt; 'dt2' DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime')), 
 ...&gt; 'dt3' DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'))
 ...&gt; );
</code></pre>

Let's insert some rows in a way that initiates automatic datetime completion:

<pre><code>sqlite&gt; INSERT INTO 'test' ('id') VALUES (null);
sqlite&gt; INSERT INTO 'test' ('id') VALUES (null);
</code></pre>

The stored data clearly shows that the first two are the same but not the third function:

<pre><code>sqlite&gt; 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
</code></pre>

Pay attention that SQLite functions are surrounded in parenthesis!
How difficult was this to show it in one example?

Have fun!

blocks|key|394773|text|您可以使用触发器。效果非常好|type|unstyled|depth|inlineStyleRanges|entityRanges|data|394774|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('%25Y-%25m-%25d+%25H:%25M:%25f',+'NOW')+WHERE+id+=+NEW.id;
END;

CREATE+TRIGGER+update_Timestamp_Trigger
AFTER+UPDATE+On+MyTable
BEGIN
+++UPDATE+MyTable+SET+Timestamp+=+STRFTIME('%25Y-%25m-%25d+%25H:%25M:%25f',+'NOW')+WHERE+id+=+NEW.id;
END;|code-block|syntax|javascript|394775|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can use triggers. works very well

<pre><code>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;
</code></pre>

I have an SQLite database, version 3 and I am using C# to create an application that uses this database.

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?

How to have an automatic timestamp in SQLite?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个SQLite数据库，版本3，并且我正在使用C#创建一个使用该数据库的应用程序。我希望在表中使用时间戳字段来实现并发，但我注意到当我插入新记录时，该字段未设置，并且为null。例如，在MS SQL Server中，如果我使用由数据库更新的时间戳字段，我不必自己设置。这在SQLite中是可能的吗？

问如何在SQLite中设置自动时间戳？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite中设置自动时间戳？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite中设置自动时间戳？
EN