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 this is updated by the database  I have not to set by myself  is this possible in SQLite

User · Answer

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

User · Answer

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

User · Answer

Reading datefunc a working example of automatic datetime completion would be   sqlite gt  CREATE TABLE  test            gt      id  INTEGER PRIMARY KEY         gt      dt1  DATETIME NOT NULL DEFAULT  datetime CURRENT TIMESTAMP   localtime             gt      dt2  DATETIME NOT NULL DEFAULT  strftime   Y- m- d  H  M  S    now    localtime             gt      dt3  DATETIME NOT NULL DEFAULT  strftime   Y- m- d  H  M  f    now    localtime           gt       Let s insert some rows in a way that initiates automatic datetime completion   sqlite gt  INSERT INTO  test    id   VALUES  null   sqlite gt  INSERT INTO  test    id   VALUES  null     The stored data clearly shows that the first two are the same but not the third function   sqlite gt  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

User · Answer

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

User · Answer

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 lt YourEntity gt                     Property b   gt  b Timestamp                   ValueGeneratedOnAddOrUpdate                    IsConcurrencyToken                    ForSqliteHasDefaultValueSql  CURRENT TIMESTAMP      It will make a default value to every data that will be insert into this table

User · Answer

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

User · Answer

If you use the SQLite DB-Browser you can change the default value in this way    Choose database structure select the table modify table 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

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

Examples related to c#

Examples related to sqlite

Examples related to timestamp