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

User · Answer

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

User · Answer

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 gt  insert into test table stamp created  stamp updated  values null  null    Query OK  1 row affected  0 06 sec   mysql gt  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 gt  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 gt  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

User · Answer

This is how can you have automatic  amp  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 lt 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 lt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  insert into test table stamp created  stamp updated  values null  null    Query OK  1 row affected  0 06 sec   mysql gt  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 gt  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 gt  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

User · Answer

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

User · Answer

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

User · Answer

This is how can you have automatic  amp  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 lt 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 lt 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

User · Answer

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

User · Answer

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

User · Answer

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  lt trigger name gt  BEFORE INSERT ON  lt table name gt  FOR EACH ROW SET NEW  lt timestamp field gt    CURRENT TIMESTAMP    MySQL Reference  http   dev mysql com doc refman 5 0 en triggers html

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt trigger name gt  BEFORE INSERT ON  lt table name gt  FOR EACH ROW SET NEW  lt timestamp field gt    CURRENT TIMESTAMP    MySQL Reference  http   dev mysql com doc refman 5 0 en triggers html

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

Examples related to mysql

Examples related to mysql-error-1293