I’ve been searching for an efficient way to set created and modified date fields in MySQL. Of course, this can be done in the application you’re writing, however, I wanted to find a way to automatically do this on the database layer.
Please note, this approach is targeted for MySQL 5.0+.
The approach I take here is a combination of using the TIMESTAMP field data type and a
IMPORTANT: When using multiple
TIMESTAMP fields in a table, there can be only one
TIMESTAMP column with
ON UPDATE clause. This is why we need to use a
TRIGGER to update one of the fields values.
In our case, we will set the
date_modified field to contain the
CURRENT_TIMESTAMP and also set the
ON UPDATE clause to
1 2 3 4 5 6
If you noticed in the
CREATE TABLE snippet above we use the
TIMESTAMP features on the
date_modified field, but set our
date_created field to
NULL by default. We do this because our
TRIGGER will populate the value before the insert.
1 2 3 4 5 6 7 8
In our trigger, we simply set the
date_created value to the
Now you will be able to insert and update rows in your table without having to specify the
Another approach that can be used is by setting the
date_created value to
NULL when inserting a row. This involves a different
CREATE TABLE syntax.
1 2 3 4 5 6
date_created field is now set to
NOT NULL with a default value of
0000-00-00 00:00:00. Here’s an important note from the documentation:
TIMESTAMP columns are NOT NULL by default, cannot contain NULL values, and assigning NULL assigns the current timestamp.
In other words, when we insert and set the value to
NULL on a
TIMESTAMP field, it will insert the current timestamp.
An example insert/update statement would look like:
This approach allows us to avoid using triggers, however requires you to specify the
NULL value when the query is executed.