MySQL create time and update time timestamp

Question

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 1

As far as I know, there's no workaround for that restriction. You'll need to manually set (at least) one of the timestamps, the easiest way is just add updated = NOW() to the UPDATE-query.

Answer 2

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.

Answer 3

You'll need two columns: CREATE_TIME and UPDATE_TIME.

You might want to add CREATE_USER and UPDATE_USER.

Perhaps you'd want to have a 1:many relationship with name of column changed, old and new values.

It's all part of change data capture. You could have CDC tables that are updated using triggers.

Answer 4

I would leave the current timestamp the way you suggested and fill in the created_at field with current date on insert.