How to create timestamp column with default value ‘now’?

How to create a table with a timestamp column that defaults to DATETIME('now')?

Like this:

CREATE TABLE test (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    t TIMESTAMP DEFAULT DATETIME('now')
);

This gives an error.

Answer

As of version 3.1.0 you can use CURRENT_TIMESTAMP with the DEFAULT clause:

If the default value of a column is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used in the new row is a text representation of the current UTC date and/or time. For CURRENT_TIME, the format of the value is “HH:MM:SS”. For CURRENT_DATE, “YYYY-MM-DD”. The format for CURRENT_TIMESTAMP is “YYYY-MM-DD HH:MM:SS”.

CREATE TABLE test (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    t TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);