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?

Answer 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.

Answer 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)  

Answer 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.

Answer 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

Answer 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.

Answer 6

As of MySQL 5.6 its easy-peasy... give it a try:

create table tweet ( 
    id integer not null auto_increment primary key, 
    stamp_created timestamp default now(), 
    stamp_updated timestamp default now() on update now(),
    message varchar(163)
)

Answer 7

This issue seemed to have been resolved in MySQL 5.6. I have noticed this until MySQL 5.5; here is an example code:

DROP TABLE IF EXISTS `provider_org_group` ;
CREATE TABLE IF NOT EXISTS `provider_org_group` (
  `id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `type` VARCHAR(100) NULL,
  `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `insert_src_ver_id` INT NULL,
  `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_src_ver_id` INT NULL,
  `version` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;

Running this on MySQL 5.5 gives:

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

Running this on MySQL 5.6

0 row(s) affected   0.093 sec

Answer 8

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() 
); 

source: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

Answer 9

i think this is the better query for stamp_created and stamp_updated

CREATE TABLE test_table( 
    id integer not null auto_increment primary key, 
    stamp_created TIMESTAMP DEFAULT now(), 
    stamp_updated TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE now() 
); 

because when the record created, stamp_created should be filled by now() and stamp_updated should be filled by '0000-00-00 00:00:00'

Answer 10

For mysql 5.7.21 I use the following and works fine:

CREATE TABLE Posts ( modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP )

Answer 11

this will add two column for creation and updation. both will get updated while inserting and updating.

   create table users( 
      id integer not null auto_increment primary key, 
      created_date timestamp default now(), 
      modified_date timestamp default now() on update now() 
    ); 

Answer 12

My web host is stuck on version 5.1 of mysql so anyone like me that doesn't have the option of upgrading can follow these directions:

http://joegornick.com/2009/12/30/mysql-created-and-modified-date-fields/