MySQL created_at and updated_at: Timestamp Columns

Almost every table in a real application carries two columns: when the row was created and when it was last changed. MySQL can maintain them for you, so that no application code can forget.

The two columns

query.sql

SQL

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(60) NOT NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Output

OUTPUT

Query OK, 0 rows affected

DEFAULT CURRENT_TIMESTAMP fills the column when a row is inserted.
ON UPDATE CURRENT_TIMESTAMP sets the column to the current time every time the row is changed.

To see this clearly, the example freezes the session clock with SET timestamp, so that every statement sees a fixed time (Date and Time Functions). This is only for demonstrations and tests.

query.sql

SQL

SET time_zone = '+00:00';

SET timestamp = UNIX_TIMESTAMP('2026-03-01 10:00:00');
INSERT INTO articles (title) VALUES ('Hello MySQL');

SET timestamp = UNIX_TIMESTAMP('2026-03-05 16:30:00');
UPDATE articles SET status = 'published' WHERE id = 1;

SELECT id, status, created_at, updated_at FROM articles;

Output

OUTPUT

+----+-----------+---------------------+---------------------+
| id | status    | created_at          | updated_at          |
+----+-----------+---------------------+---------------------+
|  1 | published | 2026-03-01 10:00:00 | 2026-03-05 16:30:00 |
+----+-----------+---------------------+---------------------+
1 row in set

created_at stays the same, and updated_at moved to the time of the update. Neither value was written by the application.

An update that changes nothing does not touch updated_at

query.sql

SQL

1
2
3

SET timestamp = UNIX_TIMESTAMP('2026-03-09 09:00:00');
UPDATE articles SET status = 'published' WHERE id = 1;
SELECT updated_at FROM articles WHERE id = 1;

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected
Rows matched: 1  Changed: 0  Warnings: 0

+---------------------+
| updated_at          |
+---------------------+
| 2026-03-05 16:30:00 |
+---------------------+
1 row in set

The statement matched one row, but changed none (the status was already published). MySQL does not count that as an update, so updated_at is unchanged: it still shows 16:30 on 5 March. This is usually what you want: “last modified” means “last really modified”.

query.sql

SQL

SELECT updated_at FROM articles WHERE id = 1;

Output

OUTPUT

+---------------------+
| updated_at          |
+---------------------+
| 2026-03-05 16:30:00 |
+---------------------+
1 row in set

DATETIME or TIMESTAMP

	`DATETIME`	`TIMESTAMP`
Range	`1000-01-01` to `9999-12-31`	`1970-01-01 00:00:01` to `2038-01-19` UTC
Storage	Stored as written, no time zone	Stored as UTC, converted using the session time zone
Size	5 bytes (more with fractions)	4 bytes (more with fractions)

TIMESTAMP stops in January 2038. Use DATETIME for anything that may reach that date (expiry dates, contracts, birthdays, “valid until”). For created_at and updated_at, both are common. If you choose DATETIME, make your application and connection use UTC consistently (Datetime vs Timestamp).

Fractional seconds

Add precision in brackets if two rows can be created in the same second and you need to order them: DATETIME(3) keeps milliseconds. The default and the on-update expression must use the same precision:

query.sql

SQL

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
);

Output

OUTPUT

Query OK, 0 rows affected

Mixing them is an error:

query.sql

SQL

CREATE TABLE events_bad (id INT, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Output

OUTPUT

ERROR 1067 (42000): Invalid default value for 'updated_at'

Who made the change

For an audit trail you often add who as well. MySQL does not know the application’s user (it only knows the database account), so the application supplies it:

query.sql

SQL

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(60) NOT NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO documents (title, created_by) VALUES ('Quarterly plan', 7);
UPDATE documents SET title = 'Quarterly plan v2', updated_by = 12 WHERE id = 1;
SELECT title, created_by, updated_by FROM documents;

Output

OUTPUT

+-------------------+------------+------------+
| title             | created_by | updated_by |
+-------------------+------------+------------+
| Quarterly plan v2 |          7 |         12 |
+-------------------+------------+------------+
1 row in set

Foreign keys on created_by and updated_by to your users table keep them valid. For a full history of changes (old and new values), use triggers or a history table (Auditing, Price History).

Which clock?

CURRENT_TIMESTAMP and NOW() use the database server’s clock, and the session time zone for display. If you run many application servers, using the database’s clock gives one consistent source, instead of trusting each server’s clock.

Pattern checklist

Put created_at and updated_at on every table that holds business data. Adding them later is much harder.
Let MySQL fill them (defaults and ON UPDATE), and do not write them from the application. That avoids clock differences and forgotten values.
Store and compare in UTC, and convert for display (Dates and Times in PHP).
Index created_at if you filter or sort on it often (feeds, reports).
Use DATETIME where 2038 could matter.

lightbulb

Do not set updated_at by hand

If application code writes updated_at = NOW() on some paths and not others, the column lies. Let ON UPDATE CURRENT_TIMESTAMP do it, and every path is covered.