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

Related Content

Subscribe to the post comments feeds or Leave a trackback