SQLite: Default a datetime field to the current time (now)

SQLite FAQ: How do I default a SQLite datetime field to the current date and time? (Also asked as, How do I default a SQLite date/time field to now?)

Solution

Use the SQLite current_timestamp function to default a SQLite field to the current date and time, like this:

last_updated datetime default current_timestamp

In a complete CREATE TABLE example I just used on an Android project, this looks like this:

create table projects (
    _id integer primary key autoincrement,
    name text not null,
    description text,
    date_created datetime default current_timestamp,
    last_updated datetime default current_timestamp
);

Also, as you can see from this example, you can use the current_timestamp function more than once in a single SQLite CREATE TABLE statement. (This doesn't seem like a big deal, but at the time of this writing, you can't do the same thing in MySQL.)

Performing the SQL INSERT

Once you create a table like that, you can insert data into it with a SQL INSERT command like this:

INSERT INTO projects (name, description) VALUES ('Project 1', 'Next excellent project');

As shown, the key to using default field values is to skip them when doing your SQL INSERT.