Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Updating entry WITHOUT updating timestamp

People also ask

What does a timestamp do on update Current_timestamp data type?

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.

How do I add a default value to a timestamp column?

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'.

Does MySQL update automatically?

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' …;