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?
This question is related to
mysql
mysql-error-1293
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
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.
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
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);
But here's the nice part:
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
)
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)
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)
)
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);
But here's the nice part:
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'
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/
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.
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)
)
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
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)
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/
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
)
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/
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
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.
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/
Source: Stackoverflow.com