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 th

Solution 1:

Just declare a default value for a field:

CREATETABLE MyTable(
    ID INTEGERPRIMARY KEY,
    Name TEXT,
    Other STUFF,
    Timestamp DATETIME DEFAULTCURRENT_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:

CREATETABLE my_table (
    id INTEGERPRIMARY KEY AUTOINCREMENT NOTNULL,
    name VARCHAR(64),
    sqltime TIMESTAMPDEFAULTCURRENT_TIMESTAMPNOTNULL
);

INSERTINTO my_table(name, sqltime) VALUES('test1', '2010-05-28T15:36:56.200');
INSERTINTO my_table(name, sqltime) VALUES('test2', '2010-08-28T13:40:02.200');
INSERTINTO 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>CREATETABLE'test' ( 
   ...>'id'INTEGERPRIMARY KEY,
   ...>'dt1' DATETIME NOTNULLDEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')), 
   ...>'dt2' DATETIME NOTNULLDEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime')), 
   ...>'dt3' DATETIME NOTNULLDEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'))
   ...> );

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

sqlite>INSERTINTO'test' ('id') VALUES (null);
sqlite>INSERTINTO'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-2609:10:08|2017-09-2609:10:08|2017-09-2609:10:08.0532|2017-09-2609:10:56|2017-09-2609:10:56|2017-09-2609: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

CREATETABLE MyTable(
ID INTEGERPRIMARY KEY,
Name TEXT,
Other STUFF,
Timestamp DATETIME);


CREATETRIGGER insert_Timestamp_Trigger
AFTER INSERTON MyTable
BEGINUPDATE MyTable SETTimestamp=STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') WHERE id = NEW.id;
END;

CREATETRIGGER update_Timestamp_Trigger
AFTER UPDATEOn MyTable
BEGINUPDATE MyTable SETTimestamp= 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.