How to Set a Timestamp Column's Default Value to the Current Time in SQLite?

Creating Timestamp Columns with Default Value 'Now'

You may have encountered an error while trying to create a table with a timestamp column that has a default value of DATETIME('now'). When you executed the following statement:

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

you received an error message.

Solution

In SQLite version 3.1.0 and later, you can utilize CURRENT_TIMESTAMP in the DEFAULT clause. By doing so, the new row will be assigned a text representation of the current UTC date and/or time.

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

When utilizing CURRENT_TIMESTAMP:

• CURRENT_TIME: Default format is "HH:MM:SS"
• CURRENT_DATE: Default format is "YYYY-MM-DD"
• CURRENT_TIMESTAMP: Default format is "YYYY-MM-DD HH:MM:SS"

The above is the detailed content of How to Set a Timestamp Column's Default Value to the Current Time in SQLite?. For more information, please follow other related articles on the PHP Chinese website!