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\u2019t do this.<\/p>\n
TIMESTAMP columns are identical to DATETIME columns with one important exception \u2014 they can be set to take the current time value when a row is created or updated.<\/p>\n
You can define more than one TIMESTAMP colum in a table \u2014 however, only ONE TIMESTAMP column in a table can be configured for Auto-Update or Auto-Initialization<\/strong>.<\/p>\n (For that reason, I usually make a practice of setting other columns to \u2018DATETIME\u2019 so there\u2019s only one \u2018TIMESTAMP\u2019 column per table.)<\/p>\n There are four options when using TIMESTAMP, they are:<\/p>\n <\/p>\n Here\u2019s an example of a complete \u2018CREATE TABLE\u2019 statement that shows how it all works:<\/p>\n Note that only one of the fields can be TIMESTAMP, that\u2019s a limitation of MySQL.<\/p>\n The choice to use the TIMESTAMP on the \u2018updated_at\u2019 field was random in this example. Choose whatever makes sense for your application.<\/p>\n\n
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP<\/pre>\n
\n
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP<\/pre>\n
\n
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP<\/pre>\n
\n
ts TIMESTAMP DEFAULT 0<\/pre>\n
CREATE TABLE `foo`.`timestamp_example` (\r\n`id` INTEGER(10) UNSIGNED AUTO_INCREMENT,\r\n`some_text_field` VARCHAR(20),\r\n`updated_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n`created_at` DATETIME DEFAULT NULL,\r\nPRIMARY KEY (id)\r\n);\r\n<\/pre>\n