MySQL Timestamps Automatically Update
By default, the TIMESTAMP field in a MySQL table will automatically update to the current timestamp if any of the other fields in the row change. This is extremely useful for situations where you want to track changes such as for an ajax system that wants to fetch any changes that have occured since it last "checked in". This can be easily demonstrated by running the following commands in a MySQL database.
Now if we select everything from the table, you should see something like below (with a different timestamp).
Now if we update a row in the table you should see that the timestamp also updated.
This could be an undesireable effect if you just want to store a timestamp for an event that happened, rather than tracking changes in a row. If this is the case, you must explicitely define the timestamp every time you make a change to the row. Alternatively, if you want the timestamp to never automatically update, you can specify the DEFAULT
for the timestamp when creating the table, and then the ON UPDATE
will never trigger. When the default is not specified (and thus ON UPDATE
is set to CURRENT_TIMESTAMP
), manually setting the value of the timestamp to a value does not stop future updates from changing the value to the current timestamp.
Warning - Explicit Defaults
When installing MySQL on Debian 7, the my.cnf
file specifies explicit_defaults_for_timestamp
which "breaks" the default behaviour above and will result in timestamps not defaulting to the current timestamp on insertion or update. However, Ubuntu 14.04, which I was using for this tutorial does not. As a rule of thumb, it is best to be very explicit and make sure to specify DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
when creating the timestamp column. That way your code should work across databases without having to remember to check the server configuration.
References
First published: 16th August 2018