mysql automatically store record creation timestamp

Question

Is there some way mysql can store timestamp automatically in a record row whenever that it is created. I was trying to use timestamp(data type) with current_timestamp as default value but then realised this will get updated everytime the record is updated. I just need something that will store create timestamp.

Thanks

Answer 1

Set the DEFAULT constraint to use CURRENT_TIMESTAMP:

CREATE TABLE ...   your_date_column DATETIME DEFAULT CURRENT_TIMESTAMP   ... 

For an existing table, use the ALTER TABLE statement:

ALTER TABLE your_table ALTER COLUMN date_column SET DEFAULT CURRENT_TIMESTAMP 

Unless you specify a value to for the date_column, the default will be the date & time the INSERT statement was run. NULL and DEFAULT or valid values to use the default constraint otherwise, assuming the column is nullable.

Answer 2

You can get the full details on timestamps in MySQL at https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html.

The point that you care about is that if you define a timestamp column as DEFAULT CURRENT_TIMESTAMP clause and don't have an ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

But be warned. The obvious thing to want to do is to have two timestamp columns, one being the creation time and the other being the last update time. Unfortunately it is a documented MySQL limitation that MySQL does not support this. I have no idea why MySQL has such an odd limitation - no other major database has problems with this common use case.