How to have an automatic timestamp in SQLite?

Question

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?

Answer 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.

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

Answer 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!

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

Update: As mentioned in the comments I tested codes on version 3.44.0, fortunately it works! why? becuase sqlite convert DATETIME datatype (from traditional SQL implementations) into NUMERIC datatype, base on determination Of Column Affinity ("https://www.sqlite.org/datatype3.html" section 3.1)

but it is better to use TEXT type instead of Datetime:

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

also tested this way, it works:).

Answer 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.

Answer 6

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

Answer 7

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.