Jason Bosco

Full Stack Web Developer ; Generalist

Two Timestamp Columns in MySQL

CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = INNODB;

That should be simple right? Apparently not:

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

I can use CURRENT_TIMESTAMP in only one place in the table. So I can’t have MySQL do what I intended it to do - maintain two timestamps, one to store the record creation timestamp and another to store record update timestamp. So I thought I’ll maintain those two timestamps by myself in the app. Give me two simple TIMESTAMP fields:

CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL,
`updated_at` TIMESTAMP NOT NULL
) ENGINE = INNODB;

MySQL automatically set the first timestamp field to DEFAULT to CURRENT_TIMESTAMP and ON UPDATE to CURRENT_TIMESTAMP. The second timestamp field was set to DEFAULT to 0. That was indeed strange.

I thought I found a bug in MySQL. But no, it turns out it is actually a feature! Actually, it’s a result of a combination of features:

  • “With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.”
  • “It need not be the first TIMESTAMP column in a table that is automatically initialized or updated to the current timestamp. However, to specify automatic initialization or updating for a different TIMESTAMP column, you must suppress the automatic properties for the first one. Then, for the other TIMESTAMP column, the rules for the DEFAULT and ON UPDATE clauses are the same as for the first TIMESTAMP column, except that if you omit both clauses, no automatic initialization or updating occurs.”

Infering from the above two points from the MySQL manual, if there are two (or more) TIMESTAMP fileds in a table with no DEFAULT and ON UPDATE clauses specified, MySQL automatically defaults the first timestamp field to have a DEFAULT CURRENT_TIMESTAMP and an ON UPDATE CURRENT_TIMESTAMP field and the rest of the timestamp fields to have a DEFAULT 0. 

Now coming back to the problem at hand, I need to track the creation and update timestamps of a record. This can be achieved using another feature:

  • By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp.

So if I assign a NULL value to a NOT NULL TIMESTAMP field, it uses the current timestamp value instead! And that’ll be my created_at field. The updated_at TIMESTAMP field can have DEFAULT CURRENT_TIMESTAMP and an ON UPDATE CURRENT_TIMESTAMP to track the update time. So just to keep it a little more obvious I now use the following CREATE TABLE:

CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = INNODB;

The corresponding INSERT statment will be:

References:

INSERT INTO test_table (id, created_at, updated_at) VALUES (1, NULL, NULL);