What is the Default Timestamp in MySQL?

MySQL’s default timestamp feature automatically initializes and updates columns to the current date and time. This guide explains how to use and configure default timestamp values in MySQL.

What is a default timestamp in MySQL?

In MySQL, the TIMESTAMP data type is used to store date and time data. A TIMESTAMP column can be automatically set to the current date and time when a record is inserted or updated. This feature is particularly useful for tracking changes in database records, like creation and last update times.

What is the syntax for defining a default timestamp?

When defining a table, you can specify default values for TIMESTAMP columns. The syntax includes the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

What is the default behavior for TIMESTAMP columns?

MySQL handles TIMESTAMP columns with no explicit default value as follows:

• The first TIMESTAMP column in a table, if not explicitly assigned a default value or nullability, defaults to DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
• Subsequent TIMESTAMP columns without explicit default values default to NULL.

How to configure default values

You can configure default timestamp values for various scenarios, such as setting custom default values or handling multiple timestamp columns.

Custom default values

You can set a specific datetime as the default value. For example, setting a default timestamp to a specific date:

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    event_date TIMESTAMP DEFAULT '2023-01-01 00:00:00'
);

How to handle multiple timestamp columns

When a table contains multiple TIMESTAMP columns, only the first one automatically receives the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP properties if not explicitly defined.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

In this example, updated_at will default to NULL unless explicitly modified.

How to disable automatic updates

To prevent a TIMESTAMP column from automatically updating, omit the ON UPDATE CURRENT_TIMESTAMP clause.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Here, last_login won’t update automatically on row modification.

Limitations and considerations

• TIMESTAMP values range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.
• Be mindful of time zone effects on TIMESTAMP values, as they are converted from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
• The automatic initialization and update to the current date and time do not occur for columns explicitly set to NULL.

Using default timestamps effectively

Default timestamps are invaluable for maintaining a historical record of database transactions. They are essential for tracking when a record was created or last updated, aiding in data integrity and analysis.