How to have an automatic timestamp in SQLite?
Solution 1
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 INSERT
command explicitly sets this field to NULL
, it will be set to NULL
.
Solution 2
You can create TIMESTAMP field in table on the SQLite, see this:
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;
Solution 3
Reading datefunc a 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?
Have fun!
Solution 4
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;
Solution 5
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:
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.
Related videos on Youtube
Álvaro García
Updated on April 16, 2022Comments
-
Álvaro García about 2 years
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 it is updated by the database and I don't have to set it by myself. Is this possible in SQLite?
-
Rory MacLeod about 11 yearsThat's not quite equivalent to SQL Server's timestamp because it's based on the system clock; if you change the clock, the value could go backwards. In SQL Server, timestamp values are always incrementing.
-
Wirsing about 11 years@Matthieu There is no
DATETIME
data type, but SQLite accepts anything as field type. -
Rafique Mohammed over 9 years@Matthieu There is DATETIME datatype mentioned in SQLite in the link which u have provided. Read 2.2 Affinity Name Examples
-
Dale Anderson about 8 yearsThis doesn't seem to cover UPDATE statements. It looks like a trigger is the only way to do this.
-
Dietmar about 7 yearsThis will give you a granularity of one second. If you care about microseconds, you can use
DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', CURRENT_TIMESTAMP))
. -
Wirsing about 7 years@Dietmar That just adds three zeros …
-
Dietmar about 7 years@CL Sorry, you're right. I incorrectly merged your answer and javed's. With
DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW', 'localtime'))
one actually gets meaningful microsecond values. -
Dietmar about 7 yearsUseful if you need microseconds; you can use DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW','localtime'))`. Just saying DEFAULT CURRENT_TIMESTAMP will only give you a granularity of one second.
-
centurian almost 7 yearsThis definitely works! pay attention to strftime() as is surrounded with parenthesis!
-
samus about 6 yearsKnow how Entity performs on Android, in a Xamarin app? Haven't gotten around to testing, though would be nice to replace DAL with.
-
modmoto over 5 yearsI do not find the package that is needed for the sqliteHasDefault. You you know which package I have to get from nuget?
-
Rafael over 5 years@Simons0n, try to use this Microsoft.EntityFrameworkCore.Metadata.Builders.PropertyBuilder namespace.
-
toing_toing over 5 yearsCannot add a column with non-constant default (ALTER TABLE "main"."xxx_data" ADD COLUMN "created_date" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)
-
Magnus over 3 yearsAnd if it complains about not being able to add a new column with a given default value, then (1) just add the column without any default value, (2) save, (3) modify the table again and enter the default value for the column created in step 1.
-
sproketboy over 3 yearsTIMESTAMP is not a real type in SQLite. Their documentation for type affinity uses DATETIME.
-
DAG about 3 years"if your INSERT command explicitly sets this field to NULL, it will be set to NULL" ... this is true until you set a not-null constraint on the field. Strangely this constraint causes sqlite3 to use the default.
-
Rocckk over 2 yearsAnd here is the link to the documentation where this CURRENT_TIMESTAMP is explained, maybe it will be useful for someone. sqlite.org/lang_createtable.html#the_default_clause What's interesting - it's not a function, it's just a keyword. sqlite.org/lang_keywords.html
-
ChesuCR about 2 yearsUhmm I have created two fields separately,
create_date
andwrite_date
. I have used your code and I get the error "too many levels of trigger recursion". I had to exclude thewrite_date
from theupdate
trigger by adding a clauseUPDATE OF
. I hope that is the correct way to do it -
ChesuCR about 2 yearsBesides, I think Timestamp field should be TEXT because DATETIME type does not exist in SQLite