Jim Jim - 1 month ago 7
MySQL Question

When is a timestamp (auto) updated?

If I have a column in a table of type

TIMESTAMP
and has as default: CURRENT_TIMESTAMP does this column get updated to the current timestamp if I update the value
of any other column in the the same row?

It seems that it does not but I am not sure if this is what should happen.

I can not understand what this means (from MySQL documentation):


If the column is auto-updated, it is automatically updated to the
current timestamp when the value of any other column in the row is
changed from its current value. The column remains unchanged if all
other columns are set to their current values. To prevent the column
from updating when other columns change, explicitly set it to its
current value. To update the column even when other columns do not
change, explicitly set it to the value it should have]2

Answer

Give the command SHOW CREATE TABLE whatever

Then look at the table definition.

It probably has a line like this

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

in it. DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP means that any update without an explicit timestamp results in an update to the current timestamp value.

You can control this default behavior when creating your table.

Or, if the timestamp column wasn't created correctly in the first place, you can change it.

ALTER TABLE whatevertable
     CHANGE whatevercolumn 
            whatevercolumn TIMESTAMP NOT NULL
                           DEFAULT CURRENT_TIMESTAMP 
                           ON UPDATE CURRENT_TIMESTAMP;

This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable without changing the timestamp, that is,

To prevent the column from updating when other columns change

then you need to issue this kind of update.

UPDATE whatevertable
   SET something = 'newvalue',
       whatevercolumn = whatevercolumn
 WHERE someindex = 'indexvalue'

And, this works with TIMESTAMP columns only, not DATETIME or DATE columns. Because the columns are TIMESTAMPs, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.