Two MySQL timestamp columns in one table

Question

I would like to create a table that has both a column for "created" and another for "updated". The column "created" will be set at insert and never change. The column "updated" will change every time a row is updated. I don't want to mess with either of these columns in the subsequent INSERT or UPDATE statements. So what should my CREATE TABLE statement look like if I start with something like this?

CREATE TABLE IF NOT EXISTS `mydb`.`mytable` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `updated` TIMESTAMP,
  `created` TIMESTAMP,
  `deleted` TINYINT DEFAULT 0,
  `notes` TEXT DEFAULT '',
  `description` VARCHAR(100)
) TYPE=innodb;

I seem to be having trouble creating a table with two TIMESTAMP columns. I don't care if the columns are TIMESTAMP or DATETIME or whatever, I just want them to be populated by MySQL without explicit instructions from the insert or update statements.

I would like to be able to do inserts like this:

INSERT INTO `mydb`.`mytable` (notes,description) VALUES ('some note','some description');

and updates like this:

UPDATE `mydb`.`mytable` SET notes=CONCAT(notes,'some more notes') WHERE id=1;

both without having to explicitly set the "created" column or set (or reset) the "updated" column in the insert or update statement.

Answer 1

Try this one to create your table:

CREATE TABLE IF NOT EXISTS db.test_table
(
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
created DATETIME DEFAULT NULL,
updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted TINYINT DEFAULT 0,
notes TEXT DEFAULT NULL,
description VARCHAR(100)
)

Note that

updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

will allow to update this field automatically.

And set this one for a trigger before inserting records:

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `db`.`on_before_insert` BEFORE INSERT
    ON `db`.`test_table`
    FOR EACH ROW BEGIN
    SET new.created = NOW();    
    END$$

DELIMITER ;

Then you can use this to insert:

INSERT INTO db.test_table(description) VALUES ("Description")

and to update your record

UPDATE db.test_table SET description = "Description 2" where Id=1

And your created and updated fields will be set appropiately.

Answer 2

News flash: In mysql, TIMESTAMP columns are always updated with now() every time any other column in the row is updated - this is a deliberate feature of this datatype.

DATETIME on the other hand does not have this weird behaviour - it's completely normal.

The answer: created must be DATETIME, but due to this bug, you also need a trigger, like this:

CREATE TABLE IF NOT EXISTS mytable (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `updated` TIMESTAMP, -- This will be updated to now(), if you don't set it or set it to null
  `created` DATETIME NOT NULL, -- This will never be magically updated once written
  `deleted` TINYINT DEFAULT 0,
  `notes` TEXT DEFAULT '',
  `description` VARCHAR(100)
) TYPE=innodb;

DELIMITER ~
CREATE TRIGGER mytable_insert_trigger
BEFORE INSERT ON mytable
FOR EACH ROW BEGIN
    SET NEW.created = CURRENT_TIMESTAMP;
END;~
DELIMITER ;

insert into mytable (notes) values ('test');
select * from mytable;
+----+---------------------+---------------------+---------+-------+-------------+
| id | updated             | created             | deleted | notes | description |
+----+---------------------+---------------------+---------+-------+-------------+
|  1 | 2011-07-05 11:48:02 | 2011-07-05 11:48:02 |       0 | test  | NULL        |
+----+---------------------+---------------------+---------+-------+-------------+