Update and create timestamps with MySQL
Apr.30, 2009
A lot of relational tables need created and update timestamps columns. I prefer having them for all tables with no exception. However, most of applications I am working on are running MySQL. MySQL has minor limitation on timestamps. Unfortunately you can create only one time stamp column that has DEFAULT NOW() value. Read more to see how to avoid this limitation.
The simplest way to do this is create the following columns in the table:
But MySQL will return the following error:
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_ in DEFAULT or ON UPDATE clause
You can use only one of the definitions in one table. However here is the way how to create both timestamps columns:
:database, howto, mysql, timestamp
The simplest way to do this is create the following columns in the table:
stamp_created timestamp default now(),
stamp_updated timestamp default now() on update now()) ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_ in DEFAULT or ON UPDATE clause
You can use only one of the definitions in one table. However here is the way how to create both timestamps columns:
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()
); mysql> insert into test_table(stamp_created, stamp_updated) values(null, null);
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5;
+----+---------------------+---------------------+
| 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)
mysql>
May 28th, 2009 on 9:17 pm
You can use triggers in tables and avoid inserting NULL in your insert statement. This would enforce the current timestamp in that column.
eg. DELIMITER $$
CREATE trigger trgrname
before insert on test_table
for each row
begin
set new.stamp_created = current_timestamp;
end$$
DELIMITER ;
May 30th, 2009 on 10:38 am
Agreed, but I prefer avoid triggers.
Both solutions have weak points.
June 18th, 2009 on 1:54 am
“Note that it is necessary to enter nulls into both columns during ‘insert’:” Actually, you only need the null for the stamp_created as the stamp_updated will get updated automatically. mysql> insert into test_table(stamp_created) values(NULL);
Query OK, 1 row affected (0.00 sec) mysql> select * from test_table; +—-+———————+———————+
| id | stamp_created | stamp_updated |
+—-+———————+———————+
| 5 | 2009-06-17 15:54:18 | 2009-06-17 15:54:18 |
+—-+———————+———————+
June 18th, 2009 on 9:58 pm
There’s a simpler way: simply make your stamp_updated column first, and set its “on update” attribute to current_timestamp, with no default value. Then follow with your stamp_created column, and set nothing for default value or “on update”. Timestamp values default to the current timestamp, according to http://www.haidongji.com/2009/01/04/interesting-things-about-timestamp-data-type-in-mysql/.
June 18th, 2009 on 10:01 pm
Oops, misread the default value thing. I just tried it, and by default it’ll insert a timestamp value of ‘0000-00-00 00:00:00′.
June 18th, 2009 on 11:00 pm
I was doing the same experiments that you mentioned in your blog. The trick above is the only one way to have both timestamps in current MySQL version.
November 12th, 2009 on 12:44 pm
thanks a lot for the great tips…
November 18th, 2009 on 12:38 pm
thnx Bogdan, good trick
November 26th, 2009 on 1:01 am
It’s crazy to have to do this. With dynamic websites, blogs, and such, it would seem obviously intrinsic that the original creation date should be maintained automatically somehow. Oh the naivety of the `nub` :). Thanks for saving me several hours work figuring this out. Regards,
sl
December 16th, 2009 on 1:09 pm
I don’t get it. If you already have a “workaround” which does force you to insert null, why don’t you insert NOW() instead of null? With trigger workaround you gain the advantage to not mention “stamp_created” at all, so I can see a point there, although I personally refrain from such trigger usage, and I rather add NOW() into my inserts, which is IMHO much better then using “null” hack described here. Because honestly, this looks more like a bug to me. If you want 0000-00-00 as default, why don’t you get it? I would be not shocked if MySQL would decide to change this behaviour later in future and “fix” it. (although I would rather welcome to have 2+ columns default NOW fix
January 22nd, 2010 on 12:29 pm
hi! for the “insert into test_table(stamp_created, stamp_updated) values(null, null); ” statement.. how would u write it in php codings so that whenever a new data is created, it will automatically include the date created.. bcux right now i tried writing the insert in mysql codings but it only shows null null for other column info.. only the date modified n created are updated.. as im using a webpage to add data it doesn shows the created time.. rather it shows the “0000-00-00 00:00:00″. only my updated column can b updated.. pls help thks!
July 14th, 2010 on 10:16 pm
Your weblog is incredibly helpful,I need to connect with u,could i sent e-mail to you?