[c#] How to have an automatic timestamp in SQLite?

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?

This question is related to c# sqlite timestamp

The answer is


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.


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;

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.


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!


If you use the SQLite DB-Browser you can change the default value in this way:

  1. Choose database structure
  2. select the table
  3. modify table
  4. in your column put under 'default value' the value: =(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.


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.

Thanks You...


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;

enter image description here


Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to sqlite

getting " (1) no such column: _id10 " error Laravel: PDOException: could not find driver auto create database in Entity Framework Core How to open .SQLite files Accessing an SQLite Database in Swift When does SQLiteOpenHelper onCreate() / onUpgrade() run? Attempt to write a readonly database - Django w/ SELinux error Android sqlite how to check if a record exists How can I add the sqlite3 module to Python? "Insert if not exists" statement in SQLite

Examples related to timestamp

concat yesterdays date with a specific time How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Pandas: Convert Timestamp to datetime.date Spark DataFrame TimestampType - how to get Year, Month, Day values from field? What exactly does the T and Z mean in timestamp? What does this format means T00:00:00.000Z? Swift - iOS - Dates and times in different format Convert timestamp to string Timestamp with a millisecond precision: How to save them in MySQL