Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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

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'
This works with TIMESTAMP and DATETIME columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMPs) When you use 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.
                DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting results using the current time. In my case I have the DEFAULT CURRENT_TIMESTAMP but on UPDATE the column is not updated. So I am not clear what you mean in this sentence
– Jim
                Sep 23, 2013 at 15:48
                See my edit where I tried to clarify.  INSERT and UPDATE default behavior are controlled separately.
– O. Jones
                Sep 23, 2013 at 21:10
                Yes, both INSERT and UPDATE operations which give the name of the timestamp column will override the default current_timestamp behavior.
– O. Jones
                Jun 30, 2015 at 11:13
                phpMyAdmin says there is an error with SQL query "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' at line 1"
– c0dehunter
                Feb 26, 2019 at 7:54
An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values.
To explain it let's imagine you have only one row:
-------------------------------
| price | updated_at          |
-------------------------------
|  2    | 2018-02-26 16:16:17 |
-------------------------------
Now, if you run the following update column:
 update my_table
 set price = 2
it will not change the value of updated_at, since price value wasn't actually changed (it was already 2).
But if you have another row with price value other than 2, then the updated_at value of that row (with price <> 3) will be updated to CURRENT_TIMESTAMP.
                Confirmed behavior as described in MySQL. Furthermore, it does not count as an INSERT if no values changed, so a PHP call like $this->pdo->lastInsertId() returns 0 (for no insert) instead of the id for the row that contains the data that stayed the same.
– OXiGEN
                Aug 7, 2019 at 8:44
                @OXiGEN - UPDATE is not INSERT, so I would not expect lastInsertId to contain a value - regardless of whether any column changes or not. lastInsertId is generally only used when INSERT a new row having an auto-increment ID column (to find out what ID was assigned); not when altering existing rows.
– ToolmakerSteve
                Aug 29, 2020 at 18:55
Adding where to find UPDATE CURRENT_TIMESTAMP because for new people this is a confusion.
Most people will use phpmyadmin or something like it.
Default value you select CURRENT_TIMESTAMP
Attributes (a different drop down) you select UPDATE CURRENT_TIMESTAMP
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.