How to create timestamp column with default value 'now'?
How to Create a Timestamp Column with a Default Value of 'NOW()' in SQL and SQLite

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.
DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
for a single column. However, ON UPDATE
is a non-standard extension in SQLite and might not be available in all versions or other databases. For a standard approach, you might use triggers.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() );
NOW()
, GETDATE()
, and SYSDATE
are common alternatives to CURRENT_TIMESTAMP
.