When is a timestamp auto updated

Question

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

User · Answer

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.

User · Answer

I think you have to define the timestamp column like this   CREATE TABLE t1        ts TIMESTAMP DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP      See here

User · 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   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

User · Answer

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

User · Answer

Add a trigger in database   DELIMITER    CREATE TRIGGER update user password    BEFORE UPDATE ON users   FOR EACH ROW     BEGIN       IF OLD password  lt  gt  NEW password THEN         SET NEW password changed on   NOW          END IF      END    DELIMITER     The password changed time will update only when password column is changed

[mysql] When is a timestamp (auto) updated?

Examples related to mysql

Examples related to sql

Examples related to timestamp