How to have an automatic timestamp in SQLite?

Question

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 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;

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!

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.