With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP , the column has the current timestamp for its default value and is automatically updated to the current timestamp.
Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.
Any server with a local installation of MySQL 5.5 and no databases will automatically update to MySQL 5.7 or newer.
You can manually set the value of the column to its current value in the update command:
UPDATE table SET x=y, timestampColumn=timestampColumn WHERE a=b;
If you don't set the value in the query, it will be updated to the current timestamp as per the table definition.
Is there a way to manually disable updating the timestamp on a special occasion? (eg: updating the entry to revise a blog post, but not to re-date it)
Sounds like you need to configure the default constraint so that it populates the column on insertion only:
DEFAULT CURRENT_TIMESTAMP
Changing it to only be this means that any revisions will not trigger the timestamp value to be updated. IE: If you created the blogpost yesterday, and corrected a typo today - the date in the column would still be for yesterday.
To make your table/timestamp auto-update:
ALTER TABLE myTable
CHANGE myTimestampColumn
myTimestampColumn TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
To make it not auto-update:
ALTER TABLE myTable
CHANGE myTimestampColumn
myTimestampColumn TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
NOTE: The "default current_timestamp" part just sets it to the current stamp at default time, since the field is not-null. You can remove both the not null and the default, if you like.
don't use timestamps, but track times manually.
if you really want to update a record and don't update it's timestamp use:
UPDATE `table` SET `timestamp` = `timestamp`, `col` = 'new data' …;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With