How to create timestamp column with default value 'now'?

Learn how to create timestamp column with default value 'now'? with practical examples, diagrams, and best practices. Covers sql, sqlite development techniques with visual explanations.

How to Create a Timestamp Column with a Default Value of 'NOW()' in SQL and SQLite

Hero image for How to create timestamp column with default value 'now'?

Learn how to automatically populate a timestamp column with the current date and time using SQL, focusing on common database systems like SQLite.

Creating a timestamp column that automatically records the current date and time when a row is inserted or updated is a common requirement in database design. This feature is crucial for tracking data changes, auditing, and maintaining historical records. This article will guide you through the process of setting up such a column, with a particular focus on SQLite, and discuss general SQL principles.

Understanding Default Timestamp Behavior

Most relational database management systems (RDBMS) provide mechanisms to define default values for columns. For timestamp columns, the goal is often to set the default to the current date and time. The exact syntax can vary between databases, but the underlying concept remains the same: use a function that returns the current timestamp.

flowchart TD
    A[Start Table Creation] --> B{Define Column 'created_at'};
    B --> C{Set Data Type 'DATETIME'};
    C --> D{Set Default Value 'CURRENT_TIMESTAMP'};
    D --> E[End Table Creation];

Flowchart for creating a timestamp column with a default value.

Implementing Default Timestamps in SQLite

SQLite offers a straightforward way to create a DATETIME column that defaults to the current timestamp. You can use the CURRENT_TIMESTAMP function as the default value. SQLite stores DATETIME values as text in the format YYYY-MM-DD HH:MM:SS by default, but it's flexible and can also store them as Julian day numbers or Unix timestamps.

CREATE TABLE my_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO my_table (name) VALUES ('First Item');
INSERT INTO my_table (name) VALUES ('Second Item');

SELECT * FROM my_table;

Creating a table with a created_at column defaulting to CURRENT_TIMESTAMP in SQLite.

When you insert a new row into my_table without specifying a value for created_at, SQLite will automatically populate it with the timestamp of the insertion. If you do provide a value for created_at, that value will be used instead of the default.

General SQL Approaches for Other Databases

While CURRENT_TIMESTAMP is widely supported, the exact function name or syntax for getting the current time might vary slightly across different SQL databases. Here are some common equivalents:

MySQL

CREATE TABLE my_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );

-- For update timestamp: CREATE TABLE another_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

PostgreSQL

CREATE TABLE my_table ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() );

-- Or with time zone: CREATE TABLE another_table ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

SQL Server

CREATE TABLE my_table ( id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(255) NOT NULL, created_at DATETIME DEFAULT GETDATE() );

-- Or for higher precision: CREATE TABLE another_table ( id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(255) NOT NULL, created_at DATETIME2 DEFAULT GETDATE() );