MySQL create time and update time timestamp

I am creating some tables where I want to store the time when a record was created and when it was last updated. I thought I could have two timestamp fields where one would have the value CURRENT_TIMESTAMP and the other would have CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. But I guess I can’t do this because you can have only 1 timestamp field with a default value in a table?

How would you recommend I get and store the two times? Thanks!

Answer

You can have two columns of type timestamp in one table.

The following works for MySQL 5.0

create table t 
(
  id integer, 
  created_at timestamp default current_timestamp, 
  updated_at timestamp
);

I think you are confusing this with SQL Server (where timestamp is not really a “time stamp” and there is indeed a limit on a single “timestamp” column)

Edit: But you will need a trigger to update the update_at column each time the row is changed.