I’ve been searching for an efficient way to set created and modified date fields in MySQL.And I found the solution by updating the date and time manually. however, I wanted to find a way to automatically do this on the database layer.
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_TIMESTAMPand also set the
ON UPDATE clause to
1 2 3 4 5 6 7 8 9 10
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.
[youtube_channel channel=UCVQ20IdtduYNOdVX_1izYXQ resource=3 cache=300 random=1 fetch=10 num=1 ratio=3 responsive=1 width=306 display=playlist thumb_quality=hqdefault autoplay=1 norel=1 nobrand=1 showtitle=none desclen=0 noanno=1 noinfo=1 link_to=none goto_txt=”Visit our YouTube channel”]