Bugzilla is not my preferred choice when choosing a bug/issue/incident tracking system : I found Jira (way) more powerful and flexible, even a the price of an increased complexity and learning curve (for the administrator).
Some times ago I worked on a bugzilla installation used mainly to track software development for a large, enterprise-level ERP.
One requirement was to display the status of the database at a particular time in the past (for reporting purposes), but Bugzilla doesn’t have such tool (it has other nice reports btw).
Exploring the (MySQL) database I found that:
- current bugs status is stored in the “bugs” table
- the table “bugs_activity” stores the changes (who, when and what) to bugs
It is therefore possible to find the value for a bug at a particular time by looking at the bugs activity table for that field either in the “added” or in the “removed” columns, or in the “bugs” table, if no activity is found for that field (ie. the value of the field is the same as its creation).
For the “bug_status” field, this gives (for jan, 01 2014 and bug # 99) :
SELECT Ifnull( ( SELECT added FROM bugs_activity bas WHERE bas.bug_id = 99 AND bas.bug_when <= '2014-01-01' AND fieldid = 9 ORDER BY bug_when DESC LIMIT 1), (Ifnull( ( SELECT removed FROM bugs_activity bass WHERE bass.bug_id = 99 AND bass.bug_when >= '2014-01-01' AND fieldid = 9 ORDER BY bug_when DESC LIMIT 1), bug_status )) ) ) AS bug_status
Repeat as necessary for every desired field.
If you are scripting, you may want to use a user-defined variable for target date
SELECT @mydate := '2014-12-17 18:00:00';
If you are looking for an array of values (i needed multiple milestones) you may find the MySQL “find_in_set” function useful:
SELECT @mymilestone := 'MyProject v1,MyProject v2'; SELECT ... WHERE FIND_IN_SET(bugs.target_milestone, @mymilestone)
Wrap up everything:
And you’re ready to go.
Thank you for reading!