Problem
Does a TIMESTAMP column in a table with the default value of CURRENT TIMESTAMP get updated to the current timestamp if the value of any other column in the same row is altered? It appears like it does not, but I’m not sure if that is the case. I’m not sure what this signifies (according to MySQL documentation):
Asked by Jim
Solution #1
Use the command SHOW CREATE TABLE to create a table.
Then take a look at the definition of the table.
It most likely contains a sentence similar to this.
logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
It contains it. DEFAULT CURRENT TIMESTAMP indicates that any INSERT that does not specify a time stamp uses the current time. ON UPDATE CURRENT TIMESTAMP, on the other hand, indicates that any update that does not include an explicit timestamp will result in an update to the current timestamp value.
When you create your table, you can change the default behavior.
You can also alter the timestamp column if it wasn’t created correctly in the first place.
ALTER TABLE whatevertable
CHANGE whatevercolumn
whatevercolumn TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
This will automatically update your timestamp column in both INSERT and UPDATE actions on the table. If you wish to update a table without modifying the timestamp, you can use this method.
Then you must publish this type of update.
UPDATE whatevertable
SET something = 'newvalue',
whatevercolumn = whatevercolumn
WHERE someindex = 'indexvalue'
The TIMESTAMP and DATETIME columns are compatible. (It only worked with TIMESTAMPs prior to MySQL version 5.6.5.) 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.
Answered by O. Jones
Solution #2
I believe you should define the timestamp column in this manner.
CREATE TABLE t1
(
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
See here
Answered by juergen d
Solution #3
When the value of any other column in the row is modified from its present value, an auto-updated column is automatically updated to the current timestamp. If all other columns are set to their current values, an auto-updated column remains unaltered.
Let’s pretend you only have one row to explain it:
-------------------------------
| price | updated_at |
-------------------------------
| 2 | 2018-02-26 16:16:17 |
-------------------------------
If you run the update column now, it should look like this:
update my_table
set price = 2
It won’t modify the value of updated at because the price value hasn’t 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.
Answered by Alexander
Solution #4
Add a trigger i
DELIMITER //
CREATE TRIGGER update_user_password
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.password <> NEW.password THEN
SET NEW.password_changed_on = NOW();
END IF;
END //
DELIMITER ;
Only when the password column is changed will the password changed time be updated.
Answered by Sibin John Mattappallil
Solution #5
Because this is a source of confusion for newcomers, I’ve added where to find UPDATE CURRENT TIMESTAMP.
The majority of individuals will use phpmyadmin or a similar program.
You choose the default value. CURRENT TIMESTAMP
You choose attributes (from a different drop down menu). CURRENT TIMESTAMP CURRENT TIMESTAMP CURRENT TIMESTAMP CURRENT_
Answered by BrinkDaDrink
Post is based on https://stackoverflow.com/questions/18962757/when-is-a-timestamp-auto-updated