SQLite create table datetime default now

In SQLite, you can create a table with a column that has a default value of the current date and time using the DATETIME data type and the DEFAULT clause. The NOW function is used to obtain the current date and time.

Syntax

Here’s the syntax for creating a table with a DATETIME column having a default value of the current date and time(CURRENT_TIMESTAMP):

CREATE TABLE your_table_name (
    id INTEGER PRIMARY KEY,
    your_datetime_column DATETIME DEFAULT CURRENT_TIMESTAMP,
    other_column1 TEXT,
    other_column2 INTEGER,
    -- add other columns as needed
);

Explanation:

your_table_name: Replace this with the desired name for your table.
id: This is an example of an INTEGER column and is commonly used as a primary key.
your_datetime_column: Replace this with the desired name for your DATETIME column.
DATETIME: Specifies the data type of the column to be DATETIME.
DEFAULT CURRENT_TIMESTAMP: This part sets the default value of the column to the current date and time when a new record is inserted.

Example

Here’s an example to illustrate the usage:

CREATE TABLE events (
    event_id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Inserting a record without specifying the event_date
INSERT INTO events (event_name) VALUES ('Meeting');

-- Retrieving the inserted record
SELECT * FROM events;

In this example, when you insert a new record into the events table without specifying a value for the event_date column, it will automatically be set to the current date and time due to the default value specified in the table definition. The SELECT * FROM events query is used to retrieve the inserted record and view the default value of the event_date column.