Programster's Blog

Tutorials focusing on Linux, programming, and open-source

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.

# Create the database/table
CREATE database test;

use test;

CREATE TABLE Persons (
    id int AUTO_INCREMENT,
    FirstName varchar(255),
    LastName varchar(255),
    LastUpdate timestamp,
    PRIMARY KEY (id)
);

INSERT INTO 
    `Persons`
SET
    `FirstName`='Programster',
    `LastName`='Page'

Now if we select everything from the table, you should see something like below (with a different timestamp).

SELECT * FROM Persons;

# Outputs
+----+-------------+----------+---------------------+
| id | FirstName   | LastName | LastUpdate          |
+----+-------------+----------+---------------------+
|  0 | Programster | Page     | 2015-03-18 10:40:52 |
+----+-------------+----------+---------------------+

Now if we update a row in the table you should see that the timestamp also updated.

UPDATE 
    `Persons`
SET
    `LastName`='Pager'
WHERE
    `FirstName`='Programster';

SELECT * FROM Persons;

+----+-------------+----------+---------------------+
| id | FirstName   | LastName | LastUpdate          |
+----+-------------+----------+---------------------+
|  0 | Programster | Pager    | 2015-03-18 10:42:14 |
+----+-------------+----------+---------------------+

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

Last updated: 25th January 2021
First published: 16th August 2018