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
ts TIMESTAMP DEFAULT 0Here’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.