How to create a TIMESTAMP/DATETIME column in MySQL to automatically be set with the current time

The TIMESTAMP data type is the only way to have MySQL automatically set the time when a row was inserted and/or updated. DATETIME columns can’t do this.

TIMESTAMP columns are identical to DATETIME columns with one important exception — they can be set to take the current time value when a row is created or updated.

You can define more than one TIMESTAMP colum in a table — however, only ONE TIMESTAMP column in a table can be configured for Auto-Update or Auto-Initialization.

(For that reason, I usually make a practice of setting other columns to ‘DATETIME’ so there’s only one ‘TIMESTAMP’ column per table.)

There are four options when using TIMESTAMP, they are:

• Auto-initialization and auto-update:

  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• Auto-initialization only:

  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

• Auto-update only:

  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

• Neither:

  ts TIMESTAMP DEFAULT 0

Here’s an example of a complete ‘CREATE TABLE’ statement that shows how it all works:

CREATE TABLE `foo`.`timestamp_example` (
`id`                  INTEGER(10) UNSIGNED AUTO_INCREMENT,
`some_text_field`     VARCHAR(20),
`updated_at`          TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_at`          DATETIME DEFAULT NULL,
PRIMARY KEY (id)
);

Note that only one of the fields can be TIMESTAMP, that’s a limitation of MySQL.

The choice to use the TIMESTAMP on the ‘updated_at’ field was random in this example. Choose whatever makes sense for your application.

Click here to go to the table of contents for all MySQL Programming Example Code