SQLite CURRENT_TIMESTAMP

In SQLite, CURRENT_TIMESTAMP is a special keyword and function used to retrieve the current date and time. It returns the current date and time in the default format of “YYYY-MM-DD HH:MM:SS.” This function is often used when inserting or updating records to capture the current timestamp.

Here’s a brief explanation of how you can use CURRENT_TIMESTAMP in SQLite:

Using CURRENT_TIMESTAMP in INSERT Statements

-- Example: Inserting a new record with the current timestamp
INSERT INTO your_table (column1, column2, timestamp_column)
VALUES ('value1', 'value2', CURRENT_TIMESTAMP);

In this example, replace your_table with the actual name of your table and adjust the column names and values accordingly.

Using CURRENT_TIMESTAMP in UPDATE Statements

-- Example: Updating a record and setting the timestamp to the current time
UPDATE your_table
SET column1 = 'new_value', timestamp_column = CURRENT_TIMESTAMP
WHERE some_condition;

Replace your_table with your actual table name, adjust the column names, and specify the condition for the update.

Retrieving Current Timestamp in a SELECT Statement

-- Example: Selecting data along with the current timestamp
SELECT column1, column2, timestamp_column, CURRENT_TIMESTAMP AS current_time
FROM your_table
WHERE some_condition;

In this example, you can include CURRENT_TIMESTAMP in the SELECT statement to retrieve the current timestamp alongside other data.

Default Value for Timestamp Column

When creating a table, you can set a default value for a timestamp column using DEFAULT CURRENT_TIMESTAMP:

-- Example: Creating a table with a timestamp column having a default value
CREATE TABLE your_table (
    id INTEGER PRIMARY KEY,
    data_column TEXT,
    timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This ensures that if you don’t explicitly provide a value for the timestamp column during an INSERT operation, it will automatically be set to the current timestamp.

In summary, CURRENT_TIMESTAMP in SQLite is a convenient way to work with date and time information, especially when you need to track when records were inserted or updated. It simplifies the process of capturing the current time without having to manually specify the date and time values.