A common requirement for any project is to track the created and updated date/time stamp information. Thankfully with MySQL this is a relatively straight forward task… well, at least at the pure database level.
It is always recommended to track when your records were created and updated within all tables in your database as you can guarantee that at the point when you need it most, if you don’t have it you’ll regret not having it. Particularly when debugging problems and investigating issues.
Databases rarely live in isolation these days and there are many ways of logging the created and updated times. For example, you could within your web application actually generate the timestamp for all of your Insert and Update statements throughout your codebase and that is one perfectly good way of doing it. But let’s be honest, who has time for that. This approach comes with a lot of overhead for basic requirements although it is often essential to go down this approach for more complex requirements. But for now, let’s keep things simple.
MySQL is extremely powerful when it comes to managing your data. The two commands we’re going to take a look at for this are;
- ON UPDATE
As you can probably imagine, this defines the default value for a column in your table. This allows you to assign a default value of your choosing based on the data type. In this case, a TIMESTAMP field which tracks the date and time. For this you can set the default to be the CURRENT_TIMESTAMP which will automatically populate your column created_date_time with the current timestamp whenever a new row is inserted.
ON UPDATE Command
And you can probably guess what this command does too…. It does something whenever a row of data is updated. You can only have one column per table that uses the ON UPDATE command so use it wisely. Again, you simply say what you want to do ON UPDATE, in this case you again want to store the CURRENT_TIMESTAMP against the field updated_date_time.
Example Alteration to a Table
Below you can see an example of how you can alter one of your database tables to have the created_date_time and updated_date_time columns with the handy MySQL features turned on so that you never have to worry about when the record was created or updated.
ALTER TABLE your_table_name ADD COLUMN created_date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE your_table_name ADD COLUMN updated_date_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;