Having both a Created and Last Updated timestamp columns in MySQL 4.0

Question

I have the following table schema;

CREATE TABLE `db1`.`sms_queue` (
  `Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Message` VARCHAR(160) NOT NULL DEFAULT 'Unknown Message Error',
  `CurrentState` VARCHAR(10) NOT NULL DEFAULT 'None',
  `Phone` VARCHAR(14) DEFAULT NULL,
  `Created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `TriesLeft` tinyint NOT NULL DEFAULT 3,
  PRIMARY KEY (`Id`)
)
ENGINE = InnoDB;

It fails with the following error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

My question is, can I have both of those fields? or do I have to manually set a LastUpdated field during each transaction?

Solution 1

From the MySQL 5.5 documentation:

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Changes in MySQL 5.6.5:

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

Solution 2

There is a trick to have both timestamps, but with a little limitation.

You can use only one of the definitions in one table. Create both timestamp columns like so:

create table test_table( 
  id integer not null auto_increment primary key, 
  stamp_created timestamp default '0000-00-00 00:00:00', 
  stamp_updated timestamp default now() on update now() 
); 

Note that it is necessary to enter null into both columns during insert:

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec)

mysql> select * from test_table; 
+----+---------------------+---------------------+ 
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)  

mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0  

mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       | 
+----+---------------------+---------------------+ 
|  3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec)  

Solution 3

You can have them both, just take off the "CURRENT_TIMESTAMP" flag on the created field. Whenever you create a new record in the table, just use "NOW()" for a value.

Or.

On the contrary, remove the 'ON UPDATE CURRENT_TIMESTAMP' flag and send the NOW() for that field. That way actually makes more sense.

Solution 4

If you do decide to have MySQL handle the update of timestamps, you can set up a trigger to update the field on insert.

CREATE TRIGGER <trigger_name> BEFORE INSERT ON <table_name> FOR EACH ROW SET NEW.<timestamp_field> = CURRENT_TIMESTAMP;

MySQL Reference: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Solution 5

This is how can you have automatic & flexible createDate/lastModified fields using triggers:

First define them like this:

CREATE TABLE `entity` (
  `entityid` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `name` varchar(255) DEFAULT NULL,
  `comment` text,
  PRIMARY KEY (`entityid`),
)

Then add these triggers:

DELIMITER ;;
CREATE trigger entityinsert BEFORE INSERT ON entity FOR EACH ROW BEGIN SET NEW.createDate=IF(ISNULL(NEW.createDate) OR NEW.createDate='0000-00-00 00:00:00', CURRENT_TIMESTAMP, IF(NEW.createDate<CURRENT_TIMESTAMP, NEW.createDate, CURRENT_TIMESTAMP));SET NEW.lastModified=NEW.createDate; END;;
DELIMITER ;
CREATE trigger entityupdate BEFORE UPDATE ON entity FOR EACH ROW SET NEW.lastModified=IF(NEW.lastModified<OLD.lastModified, OLD.lastModified, CURRENT_TIMESTAMP);

• If you insert without specifying createDate or lastModified, they will be equal and set to the current timestamp.
• If you update them without specifying createDate or lastModified, the lastModified will be set to the current timestamp.

But here's the nice part:

• If you insert, you can specify a createDate older than the current timestamp, allowing imports from older times to work well (lastModified will be equal to createDate).
• If you update, you can specify a lastModified older than the previous value ('0000-00-00 00:00:00' works well), allowing to update an entry if you're doing cosmetic changes (fixing a typo in a comment) and you want to keep the old lastModified date. This will not modify the lastModified date.

View more solutions

Comments

[David Padbury]

Is this the only way? I can't have the database look after all that detail?

[tvanfosson]

According to the MySql manual, CURRENT_TIMESTAMP is a synonym for NOW() so I don't think this will work.

[Stephen Walcher]

I'm sure you can, it's just that CURRENT_TIMESTAMP is a flag reserved for only one field. Either way, if you had that flag in there, regardless what value you have for that field when you add a record, it will always be the current timestamp, hence the name.

[David Padbury]

@tvanfosson, I think he meant as part of the insert statement.

[tvanfosson]

I see. I thought he meant in the definition. Either way, I'd be happy to be wrong.

[David Padbury]

I went with the insert NOW() way, mostly as other code might touch these tables and I don't trust people to update them correctly. :)

[Bot]

Nice this was what I was looking for. I don't want to have to rely on people added NOW() into the query!

[KacieHouser]

actually it would be better to do after insert, and set NEW.<timestamp_field> = new.<timestamp_field that actually got to have the default current_timestamp> this way both fields are consistant

[SimonSimCity]

Here's a link from the MySQL-Documentation that describes that function: dev.mysql.com/doc/refman/5.0/en/timestamp.html

[SimonSimCity]

That has changed (at least in MySQL 5.0). See the documentation: dev.mysql.com/doc/refman/5.0/en/timestamp.html

[davemyron]

Countering @SimonSimCity: The docs for 5.0 say the exact same thing as above.

[amatusko]

the first code box, when entered manually worked perfectly for me. Thanks! I wish I had enough 'karma' to upvote this answer as it saved my bacon. mmmmm saved bacon.

[Thomas]

@KacieHouser Updating of NEW row is not allowed in after trigger

[Ray]

@RobertGamble I think the more interesting question would be WHY THE HECK DON'T they've not provided a way do this very commonly requested/needed functionality.

[humble_coder]

This seems a bit arbitrary on the part of MySQL. Can anyone explain why this is the case?

[Mauricio Vargas]

Really really old comment, BUT, it finally was changed: Changes in MySQL 5.6.5 (2012-04-10, Milestone 8) Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

[Valentin Despa]

You need to make sure that stamp_created is also set as NOT NULL. MySQL will automatically replace the NULL with the current timestamp.

[matinict]

Probably great solution What i am searching create & update time issue without trigger

[dewtea]

I believe in the year 2016, the LastUpdated column will also need a default. So the above query will work when updated to the following: <pre><code>LastUpdated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`</code></pre>

[hsanders]

I can't believe it took this long for this little nuisance to be resolved. I didn't even realize they'd fixed this and it's 2018... Thanks.

[Sebastian Scholle]

Why don't you also make the stamp_created field as: stamp_created timestamp default now() instead of using a 'ZERO' value?