Mantis BT - How to change bug date & time (with all related info) with SQL

If you found this post you most certainly know Mantis BugTracker, also known as MantisBT, the award-winner open-source bug tracking platform built with PHP and MySQL. If you use it a lot, you likely already know that the software doesn't allow you to change the date and/or the time of basically anything it tracks, such as: bug entries, notes, comments, status changes. Even if you're the platform administrator, there's no chance you can do that relying to the built-in commands.

This behaviour shouldn't sound surprising at all: the main purpose of issue-tracking software is to track the user activity, including those performed by the system administrators. Altough this is generally a good thing, there are some edge-case scenarios where altering these dates could be really useful: for example, when we need to restore an old backup and fill it with the "missing" tasks lost due to a DB crash.

When such a scenario occurs, this set of queries might come to the rescue:

SET @bugID := 1;
SET @days := -3;
SET @seconds := @days * 86400;

UPDATE mantis_bug_table 
    SET date_submitted = (date_submitted + @seconds), last_updated = (last_updated + @seconds) 
    WHERE id = @bugID;
UPDATE mantis_bug_file_table 
    SET date_added = (date_added + @seconds) 
    WHERE bug_id = @bugID;
UPDATE mantis_bug_history_table 
    SET date_modified = (date_modified + @seconds) 
    WHERE bug_id = @bugID;
UPDATE mantis_bug_revision_table 
    SET `timestamp` = (`timestamp` + @seconds)
    WHERE bug_id = @bugID;
UPDATE mantis_bugnote_table 
    SET date_submitted = (date_submitted + @seconds), last_modified = (last_modified + @seconds) 
    WHERE bug_id = @bugID;

SET @bugID := 1;

SET @days := -3;

SET @seconds := @days * 86400;

UPDATE mantis_bug_table

SET date_submitted = (date_submitted + @seconds), last_updated = (last_updated + @seconds)

WHERE id = @bugID;

UPDATE mantis_bug_file_table

SET date_added = (date_added + @seconds)

WHERE bug_id = @bugID;

UPDATE mantis_bug_history_table

SET date_modified = (date_modified + @seconds)

WHERE bug_id = @bugID;

UPDATE mantis_bug_revision_table

SET `timestamp` = (`timestamp` + @seconds)

WHERE bug_id = @bugID;

UPDATE mantis_bugnote_table

SET date_submitted = (date_submitted + @seconds), last_modified = (last_modified + @seconds)

WHERE bug_id = @bugID;

The first command defines the execution's context (aka the bug ID); the second and third commands are needed to calculate the seconds to add or subtract from the actual bug dates and times stored into the Database. In our example, we're planning to put all the dates and times stored for bug #1 three-days back, meaning that it will appear as it was inserted three days earlier than we actually did.

The following queries will updated all the bug-related tables accordingly, so the time-tracking of all the related activities concerning the target bug will keep their consistency intact.

UPDATE: if you need a tool to easily calculate the time difference in days/minutes/seconds between two dates, you should really check out the great Date Calculator page at Calculator.Net!

That's it for now: happy tracking!