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

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.

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)  

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.