'created at' and 'updated at' fields

Question

This seems like a really simple one but I'm struggling to figure it out. I want a column in my database that lists when a record was first created and another column that says when it was updated. It's my understanding I should be able to do all this just using MySQL. All help is appreciated :)

This stinks still no answer, reasons I'm already starting to miss Ruby on Rails...

Answer 1

You will probably need to use a combination of the Datetime datatype and the Timestamp data type. I would set my created column as a DateTime with a DEFAULT NOW(), and my updated column as a Timestamp with DEFAULT CURRENT_TIMESTAMP and an ON UPDATE CURRENT_TIMESTAMP attribute.

Here are the docs for the Timestamp dt:

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.

With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.

Answer 2

If you can't use the timestamp fields with default attributes that Paul W has suggested, you can use AFTER INSERT and AFTER UPDATE triggers to populate the fields.

Answer 3

You will need two fields "Created" and "Updated" with type datetime. When a new entry is inserted then insert "Created" with current time stamp. When a update is happening insert "Updated" with the current time stamp, and let the "Created" field remain as it is.

For current time stamp you can use NOW() in your mysql query.