Updating entry WITHOUT updating timestamp

Updating without ticking the timestamp

Need to modify your table but not the timestamp values? Look no further. Here's what you need to do.

Defining default constraints

If your timestamp column is updated automatically on MySQL, simply set a default constraint for the timestamp without the ON UPDATE trigger.

ALTER TABLE your_table
CHANGE timestamp_column timestamp_column DATETIME DEFAULT CURRENT_TIMESTAMP; -- I solemnly swear I am up to no update


Sneaky update without mentioning timestamp

The easiest way to retain timestamp value is to exclude it while performing the update.

UPDATE your_table
SET column1 = 'new_value'
WHERE id = target_row_id;
-- Nothing to see here, timestamp


Updating with timestamp in the loop

In cases where you explicitly mention the timestamp in the query, set it to its existing value like this:

UPDATE your_table
SET column1 = 'new_value',
timestamp_column = (SELECT timestamp_column FROM your_table WHERE id = target_row_id)
WHERE id = target_row_id; --Round and round the timestamp column goes, where it stops, nobody knows


Special cases: Timestamp taming techniques

When it comes to managing timestamp during updates, standard methods might not always suffice. Here's how to handle those special cases:

Working with trigger-happy updates

If your table employs triggers to update timestamps, you'll need to deactivate them temporarily. Sorry, trigger – it's not you, it's us.

-- Disable the trigger
ALTER TABLE your_table DISABLE TRIGGER your_trigger;

-- Execute the update
UPDATE your_table
SET column1 = 'new_value'
WHERE id = target_row_id;

-- Enable the trigger
ALTER TABLE your_table ENABLE TRIGGER your_trigger; -- Welcome back, old friend!


Bypassing ORM auto-updates

Using ORM (Object-Relational Mapping) frameworks could also auto-update timestamps. Luckily, we can outsmart them:

# Example in Python with SQLAlchemy
record = session.query(YourModel).get(target_row_id)
record.column1 = 'new_value'
# A little sneak peek to get the timestamp, then close the blinds again
session.expire(record, ['timestamp_column'])
session.refresh(record)
# Mission accomplished, commit the changes
session.commit()


Preserving audit history

In cases where maintaining timestamp accuracy is paramount, consider creating a separate table for auditing. Honestly, it's like having a secret diary!

CREATE TABLE your_table_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    your_table_id INT,
    changed_column varchar(255),
    old_value varchar(255),
    new_value varchar(255),
    changed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- A timestamp we can freely update!
);


With this, each update action is recorded æ la CSI crime scene, without altering the main table's timestamp:

START TRANSACTION;

UPDATE your_table
SET column1 = 'new_value'
WHERE id = target_row_id;

INSERT INTO your_table_audit (your_table_id, changed_column, old_value, new_value)
VALUES (target_row_id, 'column1', 'old_value', 'new_value'); -- Welcome to the Audit log!

COMMIT; -- Case closed!
