9

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
Kenneth Vogt
  • 985
  • 4
  • 12
  • 28
  • You need an ON UPDATE trigger to update the "updated" column every time an update occurs. I'd answer with a complete example, but I don't know how that's done in MySQL, as I only use PostgreSQL these days. But I'm sure the MySQL manual will provide you with the full syntax. – Jonathan Hall Jul 05 '11 at 00:06
  • 1
    possible duplicate of [One Mysql Table with Multiple TIMESTAMP Columns](http://stackoverflow.com/questions/4851672/one-mysql-table-with-multiple-timestamp-columns) – Paulo Scardine Apr 27 '13 at 22:03

5 Answers5

7

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.

james_bond
  • 6,778
  • 3
  • 28
  • 34
  • you win for the most explicit answer to the question that was asked. @Dark Slipstream's answer has lots to offer too though. – Kenneth Vogt Jul 05 '11 at 00:39
  • I think that the timestamp should be used for both created and updated. With version 5.5 of mysql you can set the created timestamp to default to current_timestamp and you can set the updated timestamp to autoupdate on updates AND set the default to current timestamp. And no trigger is necessary. – Jerry Saravia Jan 17 '12 at 06:07
3

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        |
+----+---------------------+---------------------+---------+-------+-------------+
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • just as there is a nifty way to get `updated` automatically reset at every update, the issue is how to get `created` set at insertion without explicitly adding it to every insert statement. – Kenneth Vogt Jul 05 '11 at 00:33
  • As noted in @Dark Slipstream's response above, NOW() does not work with DATETIME. – Kenneth Vogt Jul 05 '11 at 01:43
  • @Bohemian officials say that is not a bug http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html – sitilge Dec 17 '15 at 11:32
  • "TIMESTAMP columns are always updated with now() every time any other column in the row is updated" -> this should be stressed in MySQL's docs, until I read this I could't understand why all timestamps in a table were getting updated to the same value on update, thanks! – jmng Sep 30 '18 at 23:06
  • in mysql versions 5.5 and 5.7 TIMESTAMP columns are only updated with now() if their default value is set to CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, otherwise the values remain unchanged when updating a different column. Also, NOW() works with DATETIME columns in 5.5 and 5.7 – Charon ME Dec 17 '19 at 14:19
2

Try this:

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

Edit: Use a trigger.

CREATE TRIGGER mytable_update
BEFORE UPDATE ON mydb.mytable
    FOR EACH ROW SET NEW.updated = NOW();
Nahydrin
  • 13,197
  • 12
  • 59
  • 101
  • I don't think DATETIME can be DEFAULT NOW() – Andreas Jansson Jul 05 '11 at 00:12
  • @Dark Slipstream, what would the row look like right after insertion but before any update? Would mydb.mytable.updated have any value? Do we also need a trigger to set the initial value? – Kenneth Vogt Jul 05 '11 at 00:28
  • If your using an insert query, just use NOW() in the query. In your update query, you would not even mention updated. – Nahydrin Jul 05 '11 at 00:29
  • @Dark Slipstream, the whole point of my question is to not have to address either `created` or `updated` in any queries after creating the table. Triggers are cool too, so long as we never have to think about it again. – Kenneth Vogt Jul 05 '11 at 00:35
  • `created` will be given the `CURRENT_TIMESTAMP` value when the record is created. If you use the trigger, then `update` will only be given a value during each update. – Nahydrin Jul 05 '11 at 00:37
  • Dark Slipstream, this answer isn't as good as james_bond's answer. Having the `updated` column be a `TIMESTAMP` is much more pragmatic as it's MySQL automatic behavior to re-timestamp a field of this datatype when the row is updated. Using a trigger for the `created` field is much more appropriate. – maček Jul 05 '11 at 00:49
1

alternative is to change the order of timestamp column

OR

set first column DEFAULT value like this

ALTER TABLE `tblname` CHANGE `first_timestamp_column` 
     `first_timestamp_column` TIMESTAMP NOT NULL DEFAULT 0;

Reference

xkeshav
  • 53,360
  • 44
  • 177
  • 245
0

Unfortunately MySQL doesn't let you have two TIMESTAMP columns in one table. I would use ON UPDATE CURRENT_TIMESTAMP for the updated column and set created manually using the NOW() function.

Andreas Jansson
  • 3,137
  • 2
  • 30
  • 40